SQL Server删除全表、TRUNC、DBCC重设种子后自增主键仍报重复错误

根因分析 #

• 自增序列元数据损坏
  你之前的自增主键达到INT类型上限（2147483647）后执行RESEED操作时，若此时存在活跃会话操作该表，很容易导致系统表sys.identity_columns中记录的自增序列最新值last_value与表内实际最大主键值不一致。当last_value小于表内实际存在的最大主键值时，自增序列生成的新值会和已有主键重复，触发冲突报错。
• 连接池会话遗留配置异常
  应用端使用的连接池会复用数据库会话，会话级配置（如IDENTITY_INSERT开关、事务隔离级别、未提交的隐式事务等）不会在连接复用的时候自动重置。如果旧会话曾经打开过该表的IDENTITY_INSERT开关未关闭，或者有未回滚的插入失败事务，会导致后续插入操作的自增序列生成逻辑异常。而SSMS使用独立会话操作，不受连接池遗留配置影响，因此手动插入正常。
• DBCC CHECKIDENT的行为差异
  SQL Server中DBCC CHECKIDENT的RESEED效果和表的历史操作有关：如果表曾经写入过数据（哪怕已通过DELETE清空），执行DBCC CHECKIDENT(表名, RESEED, N)后，下一条插入的自增值为N + 自增增量；如果表是刚创建或者被TRUNCATE过，下一条插入的自增值就是N。如果对RESEED后的预期值判断错误，也可能导致生成的主键和已有数据冲突。

解决方案 #

验证步骤 #

先执行以下命令确认问题根源：

1. 查询自增序列元数据：

SELECT 
IDENT_CURRENT('Ur_Imported_CourseEnroll') AS 当前自增下一个值,
IDENT_SEED('Ur_Imported_CourseEnroll') AS 初始种子值,
IDENT_INCR('Ur_Imported_CourseEnroll') AS 自增增量,
last_value AS 系统表记录的最新自增值
FROM sys.identity_columns 
WHERE object_id = OBJECT_ID('Ur_Imported_CourseEnroll')

2. 查询表内实际最大主键值：

SELECT MAX(你的主键列名) AS 表内最大主键值 FROM Ur_Imported_CourseEnroll

如果当前自增下一个值小于表内最大主键值，即可确认是自增元数据损坏问题。

修复方案 #

• 方案1（修复原表）：

1. 先暂停所有访问该表的应用服务，清空应用连接池，确保无活跃会话操作该表
2. 执行TRUNCATE TABLE Ur_Imported_CourseEnroll，TRUNCATE会直接清空表数据并将自增序列重置为初始种子值，比DELETE+RESEED的可靠性更高
3. 再次执行RESEED校准自增序列：DBCC CHECKIDENT ('Ur_Imported_CourseEnroll', RESEED, 0)，执行后下一条插入的自增值即为1
4. 重新启动应用服务验证插入逻辑

• 方案2（彻底解决）：
  如果方案1执行后仍然报错，说明原表的系统元数据已经出现不可逆损坏，你采用的「导出建表脚本新建表、切换应用指向新表」的操作就是最优解，新表的自增元数据是全新生成的，不存在历史损坏问题。

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