身份增量在 SQLServer 数据库中跳转

在 SQLServer2012数据库中“ ReceiptNo”列中的一个表 Fee中，身份增量突然开始跳到100，而不是1，这取决于以下两点。

  1. 如果是1205446就跳到1206306如果是1206321就跳到1207306如果是1207314就跳到1208306。我想让你们注意的是，最后三位数字保持不变，即306，每当跳跃发生时，如下图所示。

  2. 当我重新启动计算机时，就会出现这个问题

enter image description here

84991 次浏览
小开
最佳答案

由于 SQLServer2012以来的性能改进，您遇到了这种行为。

默认情况下，在为 int列分配 IDENTITY值并重新启动服务时，缓存大小为1,000，这样可能会“丢失”未使用的值(对于 bigint/numeric，缓存大小为10,000)。

这是在 文件中提到的

SQLServer 可能由于性能原因而缓存标识值，并且 在数据库故障或 服务器重新启动。这可能导致标识值在 如果不能接受间隙，则应用程序应使用其 自己的机制来生成键值 NOCACHE选项可以将差距限制在永远不会出现的交易上 坚定不移。

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

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

如果这是因为某种原因对你来说是一个真正的问题一些可能的变通方法是..。

  1. 例如，可以使用 SEQUENCE而不是标识列，定义较小的缓存大小，并在列的默认值中使用 NEXT VALUE FOR。
  2. 或者应用跟踪标志272，这使得 IDENTITY分配记录到2008R2版本。这在全局范围内适用于所有数据库。
  3. 或者，对于最近的版本，执行 ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF以禁用特定数据库的标识缓存。

您应该知道，这些变通方法都不能保证没有间隙。IDENTITY从未保证过这一点，因为只有通过序列化表的插入才能做到这一点。如果需要一个无间隙列，则需要使用与 IDENTITY或 SEQUENCE不同的解决方案

小开

跳转身份值有许多可能的原因。它们的范围从回滚插入到用于复制的标识管理。你这种情况下是什么导致的，我不知道，如果不在你的系统里花些时间的话。

但是，您应该知道，在任何情况下都不能假定标识列是连续的。有太多的东西可以造成间隙。

你可以在这里找到更多的信息: http://sqlity.net/en/792/the-gap-in-the-identity-value-sequence/

小开

重新启动 SQLServer 后会发生此问题。

解决办法是:

  • 运行 SQLServer 配置管理器。

  • 选择 SQLServer 服务。

    SQL Server Configuration Manager

  • 右键单击 SQL Server并选择 物业。

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

    SQL Server startup parameters

小开

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

在主数据库中创建以下存储过程。

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 序列解决方案将是一项巨大的工作。序列解决方案只有在您从头开始设计 SQL2012 + 中的表时才是好的

底线是，如果您使用的是 SqlServer2008R2，那么请继续关注它。说真的，继续查。直到微软承认他们引入了一个巨大的错误，这仍然存在，甚至在 Sql 服务器2016，那么我们不应该升级，直到他们拥有它和 FIX IT。

微软直接引入了一个突破性的改变，即他们破坏了一个不再按设计工作的 API，因为他们的系统在重启时忘记了他们当前的身份。不管有没有缓存，这都是不可接受的，微软开发人员布莱恩需要拥有它，而不是告诉全世界它是“设计”和“功能”。当然，缓存是一个特性，但是不知道下一个标识应该是什么，这不是一个特性。这是该死的虫子! ! ！

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

相反，这是我可耻的黑客行为(但不像微软引入的 POS 漏洞那样可耻)。

解决方法:

在执行插入命令之前，只需在每次插入之前重新播种您的身份。只有在对 SqlServer 实例没有管理控制的情况下，才建议使用此修复程序，否则我建议在重新启动服务器时重新播种。

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+你可以使用 更改数据库范围配置:

IDENTITY _ Cache = { ON | OFF }

在数据库级别启用或禁用标识缓存 标识缓存用于提高 表的标识列。 < strong > 以避免 在服务器意外重新启动或 故障转移到辅助服务器时，禁用 IDENTITY _ CACHE 选项。 此选项类似于现有的 SQLServer 跟踪标志272, 除了可以在数据库级别而不是仅在 服务器级别。

(...)

设置 IDENTITY _ Cache

此示例禁用标识缓存。

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ;

提交答案