SQL Server中已填充数据的表如何正确重置identity标识种子

原因说明 #

DBCC CHECKIDENT 仅会重置后续新插入数据的标识列起始值，不会修改表中已存在的记录ID，这是SQL Server的默认设计，因此你之前的操作无法修改旧数据的ID。

实现方案 #

你可以根据自己的场景选择以下两种方案操作，操作前请务必先备份全表数据： #

方案1：迁移数据到新表（适合无外键关联的场景）

操作步骤如下：

1. 新建一个和Person表结构完全一致的空表Person_New，保留Id列的标识属性
2. 按原表Id升序将非标识字段数据导入新表，新表的Id会自动从1开始连续生成：

INSERT INTO Person_New (PersonName)
SELECT PersonName FROM Person ORDER BY Id ASC;

3. 确认新表数据无误后，替换原表：

DROP TABLE Person;
EXEC sp_rename 'Person_New', 'Person';

方案2：直接更新原表ID（适合需要保留原表结构的场景）

操作步骤如下：

1. 临时开启标识列手动修改权限：

SET IDENTITY_INSERT Person ON;

2. 基于原Id的排序生成连续新Id并更新：

WITH OrderedPerson AS (
    SELECT Id, ROW_NUMBER() OVER(ORDER BY Id ASC) AS NewId
    FROM Person
)
UPDATE OrderedPerson SET Id = NewId;

3. 重置标识种子保证后续插入的ID连续，关闭标识列手动修改权限：

DBCC CHECKIDENT ('dbo.Person', RESEED, (SELECT MAX(Id) FROM Person));
GO
SET IDENTITY_INSERT Person OFF;

注意事项 #

• 如果Person表的Id字段被其他表用作外键关联，需要先同步更新外键表的关联值，或者临时删除外键约束，操作完成后再重建
• 数据量较大时建议在业务低峰期操作，避免锁表影响线上业务

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