(2008/1/11已更新,附上解决方案,可以自动修复标识列的编码规则)
最近在合并异地SQL数据的时候,发现MSSQL的标识列在合并数据时,可以启用标识插入也可以不启用标识插入,两种方法都可能存在一些潜在的问题,最严重的是导入操作改变了目的表的标识列编号规则。
场景:A地的A表,B地有B表,数据结构除了标误列的种子不一样外其它完全一样:
A(ID
IDENTITY (1, 2), Content varchar(50)) 生成奇数主键ID
B(ID IDENTITY (2, 2), Content varchar(50)) 生成偶数主键ID
--
A地的A表
CREATE
TABLE
dbo.A
(
ID
int
NOT
NULL
IDENTITY
(
1
,
2
),
Content
varchar
(
50
)
NOT
NULL
)
ON
[
PRIMARY
]
GO
ALTER
TABLE
dbo.A
ADD
CONSTRAINT
PK_A
PRIMARY
KEY
CLUSTERED
(
ID
)
ON
[
PRIMARY
]
--
在A表中插入两条数据:预先插入A表两条数据
INSERT
INTO
[
dbo
]
.
[
A
]
(
[
Content
]
)
VALUES
(
'
A1
'
)
--
生成1, 'A1'
INSERT
INTO
[
dbo
]
.
[
A
]
(
[
Content
]
)
VALUES
(
'
A3
'
)
--
生成3, 'A3'
--
B地的B表
CREATE
TABLE
dbo.B
(
ID
int
NOT
NULL
IDENTITY
(
2
,
2
),
Content
varchar
(
50
)
NOT
NULL
)
ON
[
PRIMARY
]
GO
ALTER
TABLE
dbo.B
ADD
CONSTRAINT
PK_B
PRIMARY
KEY
CLUSTERED
(
ID
)
ON
[
PRIMARY
]
![](/Images/OutliningIndicators/None.gif)
--
在B表中插入1条数据:预先插入B表1表数据
INSERT
INTO
[
dbo
]
.
[
B
]
(
[
Content
]
)
VALUES
(
'
B2
'
)
--
生成2, 'B2'
现在要将A表数据导入到B表,问题出现了:
1. 不启用标识插入的问题:
不启用标识,B表会增加两条数据
4,'A1'
6,'A3'
造成同一数据在两地数据库表中的不一致情况,以及无法判断或者找出哪些数据已合并,哪些数据已修改或已删除,哪些数据重复导入等等问题。
2. 启用标识插入的问题:
启用标识后,B表会增加两条数据
1,'A1'
3,'A3'
用户最不能接受的错误出现了,B表录入的新数据是
5, '从A表导入两条数据后录入B表的新数据1'
7, '从A表导入两条数据后录入B表的新数据2'
导入合并操作改变了B表的标识列编号规则。
2008-1-11,今天终于搞定了,一个存储过程完成解决,自动修复标识列的编码规则。
上例中,调用dbo.sp_RepairIDENTITY 'B', 'ID' 即可,执行结果如下:
当前最大值:3
标识种子:2
标识增量:2
当前标识值:3
需设新标识值:4
检查标识信息: 当前标识值 '3',当前列值 '4'。
DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。
结果:已修复标识,当前标识值重设为4
代码如下:
CREATE
PROCEDURE
dbo.sp_RepairIDENTITY
(
@Tablename
nvarchar
(
255
),
--
表名
@RowID_Name
nvarchar
(
20
)
--
标识列名
)
as
SET
NOCOUNT
ON
--
Select @TableName=N'B',@RowID_Name=N'ID'
--
获取最大标识值MaxID
Declare
@sql
nvarchar
(
4000
),
@MaxID
bigint
Set
@sql
=
N
'
Select @MaxID =Max(
'
+
@RowID_Name
+
N
'
) from
'
+
@TableName
Exec
sp_Executesql
@sql
, N
'
@MaxID bigint output
'
,
@MaxID
output
--
print @MaxID
Declare
@IDENT_CURRENT
bigint
,
@NewIDentity
bigint
,
@IDENT_SEED
int
,
@IDENT_INCR
int
Select
@IDENT_SEED
=
IDENT_SEED
(
@TableName
),
--
标识种子
@IDENT_INCR
=
IDENT_INCR
(
@TableName
),
--
标识增量
@IDENT_CURRENT
=
IDENT_CURRENT(
@TableName
),
--
当前标识值
@NewIDentity
=
@MaxID
-
@MaxID
%
cast
(
IDENT_SEED
(
@TableName
)
as
int
)
+
IDENT_INCR
(
@TableName
)
--
需要设置的新标识值
Print
'
当前最大值:
'
+
Cast
(
@MaxID
as
nvarchar
(
20
))
Print
'
标识种子:
'
+
Cast
(
@IDENT_SEED
as
nvarchar
(
20
))
Print
'
标识增量:
'
+
Cast
(
@IDENT_INCR
as
nvarchar
(
20
))
Print
'
当前标识值:
'
+
Cast
(
@IDENT_CURRENT
as
nvarchar
(
20
))
Print
'
需设新标识值:
'
+
Cast
(
@NewIDentity
as
nvarchar
(
20
))
![](/Images/OutliningIndicators/None.gif)
--
----关键代码:启用标识导入数据或手工执行过DBCC CHECKIDENT('table_name',RESEED new_reseed_value)就会造成编码规则改变,所以要修复编码规则
if
(
@IDENT_CURRENT
<
@MaxID
or
@IDENT_CURRENT
%
@IDENT_SEED
>
0
)
begin
DBCC
CHECKIDENT (
@TableName
, RESEED,
@NewIDentity
)
print
'
结果:已修复标识,当前标识值重设为
'
+
Cast
(
@NewIDentity
as
nvarchar
(
20
))
end
else
begin
Print
'
结果:不需要重设标识列。
'
end
![](/Images/OutliningIndicators/None.gif)
SET
NOCOUNT
OFF