在保留永久行的情况下重置SQL Server表的IDENTITY列

这是个很典型的IDENTITY列因大量临时数据删插导致增长失控的场景，先帮你分析下之前两种方法失败的原因，再给出安全可行的解决思路。

为什么之前的方法行不通？ #

• 直接更新IDENTITY列：SQL Server默认不允许直接修改IDENTITY列的值，即便你开启SET IDENTITY_INSERT ON强行修改，更新聚集主键也会带来巨大的IO开销，而且完全没必要——毕竟临时行本来就会被删除，不需要调整永久行的主键。
• 直接RESEED到1：因为你的永久行已经占用了1到1000的Idx值，重置种子到1后，新插入行时IDENTITY会从2开始，必然和已有的永久行主键冲突，导致插入失败。

正确的实现步骤 #

在你的存储过程中，删除临时行之后、执行计算插入之前，添加以下逻辑，就能完美解决问题：

1. 获取永久行的最大主键值
   先查询出所有永久行的最大Idx，确定新行的起始点：

   DECLARE @MaxPermanentIdx BIGINT;
   SELECT @MaxPermanentIdx = ISNULL(MAX(Idx), 0) FROM [dbo].[MyTable] WHERE [Permanent] = 1;
   

   用ISNULL是为了兜底极端情况（比如还没有插入永久行的场景），根据你的描述，这里实际会返回1000。

2. 重置IDENTITY种子到最大永久Idx
   使用DBCC CHECKIDENT将种子值设置为刚才获取的最大值，这样下一次插入新行时，IDENTITY会自动从@MaxPermanentIdx + 1开始：

   DBCC CHECKIDENT ('[dbo].[MyTable]', RESEED, @MaxPermanentIdx);
   

效果说明 #

每次执行存储过程时：

• 第一步删除所有临时行，表中仅保留1000条永久行
• 重置IDENTITY种子到永久行的最大Idx（1000）
• 插入新的50万条临时行时，它们的Idx会从1001开始，而不是延续之前暴涨的数值
• 下次执行时，删除临时行后再次重置种子到1000，新插入的临时行又从1001开始

这样一来，Idx的最大值永远只会停留在1000 + 500000 = 501000，不会每天持续增长，彻底解决了IDENTITY值快速耗尽的问题，同时完全保留了永久行的原有数据和主键。

额外注意事项 #

• 确保执行DBCC CHECKIDENT时，表中已经没有临时行（你的存储过程第一步已经完成了删除，这点无需额外处理）
• 如果未来永久行有新增或删除，这个逻辑依然有效，因为MAX(Idx)会自动获取最新的最大主键值
• 全程不需要修改永久行的任何数据，避免了主键更新带来的性能损耗和数据一致性风险

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