标识增量在 SQL Server 数据库中跳转

由于自 SQL Server 2012 以来的性能改进，您遇到此行为。

现在，当为列分配值时，它默认使用 1,000 的缓存大小，重新启动服务可能会“丢失”未使用的值（/ 的缓存大小为 10,000）。IDENTITYintbigintnumeric

这在文档中有所提及

出于性能原因，SQL Server 可能会缓存标识值，并且 在数据库故障期间，某些分配的值可能会丢失，或者 服务器重新启动。这可能会导致标识值出现缺口 插入。如果间隙不可接受，则应用程序应使用其 自己的机制来生成键值。将序列生成器与 该选项可以将差距限制为从未出现过的交易 承诺。NOCACHE

从您显示的数据来看，这似乎发生在 12 月 22 日的数据输入之后，然后当它重新启动时，SQL Server 保留了值。在完成 12 月 24 日至 25 日的数据输入后，再次重新启动，SQL Server 保留了 28 日条目中可见的下一个范围。1206306 - 12073051207306 - 1208305

除非您以异常频繁的频率重新启动服务，否则任何“丢失”的值都不太可能对数据类型允许的值范围产生任何重大影响，因此最好的策略是不要担心它。

如果由于某种原因这对您来说是一个真正的问题，那么一些可能的解决方法是......

1. 您可以使用 SEQUENCE 代替标识列，并定义较小的缓存大小，例如，在列中默认使用。NEXT VALUE FOR
2. 或者应用跟踪标志 272，使分配记录为 2008 R2 之前的版本。这适用于全局所有数据库。IDENTITY
3. 或者，对于最新版本，执行以禁用特定数据库的身份缓存。ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF

您应该知道，这些解决方法都不能保证没有差距。IDENTITY 从未保证过这一点，因为只有将插入序列化到表中才能实现（如果没有这种序列化，标识值可以分配给最终失败的插入语句 - 例如，由于违反约束。或者简单回滚的事务）。如果您需要真正无间隙的列，则需要使用与 IDENTITY 或 SEQUENCE 不同的解决方案（例如）

跳跃标识值的可能原因有很多。它们的范围从回滚插入到用于复制的身份管理。是什么原因导致了这种情况，除非在您的系统中花费一些时间，我就无法判断。

但是，您应该知道，在任何情况下都不能假定标识列是连续的。有太多的事情会导致差距。

您可以在此处找到有关此内容的更多信息： http://sqlity.net/en/792/the-gap-in-the-identity-value-sequence/

重新启动 SQL Server 后，会出现此问题。

解决方案是：

• 运行 SQL Server 配置管理器。

• 选择“SQL Server 服务”。

  

• 右键单击“SQL Server”，然后选择“属性”。

• 在打开的窗口中，在“启动参数”下，键入并单击“添加”，然后按“应用”按钮并重新启动。-T272

  

我知道我的回答可能迟到了。但是我通过在 SQL Server 2012 中添加启动存储过程以另一种方式解决了问题。

在 master DB 中创建以下存储过程。

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ResetTableNameIdentityAfterRestart]
AS
BEGIN

begin TRAN
    declare @id int = 0
    SELECT @id =  MAX(id) FROM [DatabaseName].dbo.[TableName]
    --print @id
    DBCC CHECKIDENT ('[DatabaseName].dbo.[TableName]', reseed, @id)
Commit

END

然后使用以下语法将其添加到“启动”中。

EXEC sp_procoption 'ResetOrderIdentityAfterRestart', 'startup', 'on';

如果您的表很少，这是一个好主意。但是，如果您必须对许多表执行操作，此方法仍然有效，但不是一个好主意。

这仍然是许多开发人员和应用程序（无论大小）中非常普遍的问题。

不幸的是，上面的建议并不能解决所有情况，即共享主机，你不能依赖你的主机来设置 -t272 启动参数。

此外，如果您有将这些标识列用于主键的现有表，则删除这些列并重新创建新列以使用 BS 序列解决方法是一项巨大的努力。仅当您在 SQL 2012+ 中从头开始设计新表时，序列解决方法才有效

底线是，如果您使用的是 SQL Server 2008R2，那么请继续使用它。说真的，坚持下去。直到 Microsoft 承认他们引入了一个巨大的错误，即使在 Sql Server 2016 中仍然存在，那么在他们拥有它并修复它之前，我们不应该升级。

Microsoft直接引入了一个重大更改，即他们破坏了一个不再按设计工作的工作API，因为他们的系统在重新启动时忘记了其当前身份。缓存或没有缓存，这是不可接受的，名为 Bryan 的 Microsoft 开发人员需要拥有它，而不是告诉世界它是“设计”和“功能”。当然，缓存是一项功能，但忘记下一个标识应该是什么，就不是功能了。这是一个令人毛骨悚然的错误!!

我将分享我使用的解决方法，因为我的数据库位于共享主机服务器上，而且，我不会删除和重新创建我的主键列，这将是一个巨大的 PITA。

相反，这是我可耻的黑客（但不像微软引入的这个 POS 错误那样可耻）。

破解/修复：

在插入命令之前，只需在每次插入之前重新设定标识的种子即可。仅当您对 SQL Server 实例没有管理员控制权时，才建议使用此修复程序，否则我建议在重新启动服务器时重新设定种子。

declare @newId int -- where int is the datatype of your PKey or Id column
select @newId = max(YourBuggedIdColumn) from YOUR_TABLE_NAME
DBCC CheckIdent('YOUR_TABLE_NAME', RESEED, @newId)

只需插入前的那 3 行，您就可以开始了。它真的不会对性能产生太大影响，也就是说，它不会被注意到。

祝你好运。

在 SQL Server 2017+ 中，可以使用 ALTER DATABASE SCOPED CONFIGURATION：

IDENTITY_CACHE = { ON |关闭 }

在数据库级别启用或禁用标识缓存。默认 标识缓存用于提高 具有 Identity 列的表。为了避免 标识列（在服务器意外重新启动的情况下）或 故障转移到辅助服务器，禁用IDENTITY_CACHE选项。此选项类似于现有的 SQL Server 跟踪标志 272。 除了它可以在数据库级别设置，而不仅仅是在 服务器级别。

(...)

G. 设置IDENTITY_CACHE

此示例禁用标识缓存。

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ;