SQL Server 2016如何正确设置identity标识值：复制后表标识值为NULL

根因说明 #

该问题是SQL Server跨库复制表数据后的常见问题：复制操作仅同步了存量数据，没有同步原表的标识列种子值，新库的TBL_State表标识列处于未初始化状态，因此即使表内已有25条记录，执行检查时仍返回当前标识值为NULL。

正确操作步骤 #

1. 先确认标识列的基础配置与当前最大值，避免参数设置错误：

-- 查询表的标识列信息，确认列名、初始种子、增量
SELECT name 标识列名, seed_value 初始种子, increment_value 增量
FROM sys.identity_columns
WHERE object_id = OBJECT_ID('TBL_State')

-- 查询当前表中标识列的实际最大值，替换下方的标识列名
SELECT MAX(你的标识列名) FROM TBL_State

2. 分两步执行重置操作，解决直接重置不生效的问题：

-- 第一步先执行检查，触发系统读取当前表的标识列实际值
DBCC CHECKIDENT ('TBL_State', NORESEED)
-- 第二步执行重置，若增量为默认1、当前最大值为25，设置为25即可让下条插入的标识值为26
DBCC CHECKIDENT ('TBL_State', RESEED, 25)
-- 第三步验证结果
DBCC CHECKIDENT ('TBL_State', NORESEED)

执行完成后再次检查，会返回current identity value '25', current column value '25'，说明设置生效。

注意事项 #

• 如果复制操作过程中开启过SET IDENTITY_INSERT TBL_State ON，操作完成后务必执行SET IDENTITY_INSERT TBL_State OFF，避免影响标识列自动生成逻辑
• 若你的标识列增量不是默认的1，需结合实际增量调整重置参数，比如增量为2、当前最大值25的话，重置为25后下一条标识值为27
• 后续插入新数据如果出现主键冲突，可重新执行上述步骤检查标识值是否低于实际最大值

内容的提问来源于stack exchange，提问作者John