身份增量在 SQL Server 数据库中跳跃

Identity increment is jumping in SQL Server database

提问by kashif

In one of my tables Feein column "ReceiptNo" in SQL Server 2012 database identity increment suddenly started jumping to 100s instead of 1 depending on the following two things.

在FeeSQL Server 2012 数据库标识增量中的“ReceiptNo”列中的一个表中，突然开始跳到 100s 而不是 1，具体取决于以下两件事。

1. if it is 1205446 it is jumps to 1206306, if it is 1206321, it jumps to 1207306 and if it is 1207314, it jumps to 1208306. What I want to make you note is that the last three digits remain constant i.e 306 whenever the jumping occurs as shown in the following picture.

2. this problem occurs when I restart my computer

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

2. 当我重新启动计算机时出现此问题

回答by Martin Smith

You are encountering this behaviour due to a performance improvement since SQL Server 2012.

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

It now by default uses a cache size of 1,000 when allocating IDENTITYvalues for an intcolumn and restarting the service can "lose" unused values (The cache size is 10,000 for bigint/numeric).

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

This is mentioned in the documentation

这是在文档中提到的

SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use its own mechanism to generate key values. Using a sequence generator with the NOCACHEoption can limit the gaps to transactions that are never committed.

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

From the data you have shown it looks like this happened after the data entry for 22 December then when it restarted SQL Server reserved the values 1206306 - 1207305. After data entry for 24 - 25 December was done another restart and SQL Server reserved the next range 1207306 - 1208305visible in the entries for the 28th.

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

Unless you are restarting the service with unusual frequency any "lost" values are unlikely to make any significant dent in the range of values allowed by the datatype so the best policy is not to worry about it.

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

If this is for some reason a real issue for you some possible workarounds are...

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

1. You can use a SEQUENCEinstead of an identity column and define a smaller cache size for example and use NEXT VALUE FORin a column default.
2. Or apply trace flag 272 which makes the IDENTITYallocation logged as in versions up to 2008 R2. This applies globally to all databases.
3. Or, for recent versions, execute ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFFto disable the identity caching for a specific database.

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

You should be aware none of these workarounds assure no gaps. This has never been guaranteed by IDENTITYas it would only be possible by serializing inserts to the table. If you need a gapless column you will need to use a different solution than either IDENTITYor SEQUENCE

您应该知道这些变通办法都不能保证没有差距。这从未得到保证，IDENTITY因为只有通过将插入序列化到表才能实现。如果你需要一个无缝列，您需要使用不同的解决方案比任何IDENTITY或SEQUENCE

回答by Harun ERGUL

This problems occurs after restarting the SQL Server.

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

The solution is:

解决办法是：

• Run SQL Server Configuration Manager.

• Select SQL Server Services.

  

• Right-click SQL Serverand select Properties.

• In the opening window under Startup Parameters, type -T272and click Add, then press Applybutton and restart.

  

• 运行SQL Server 配置管理器。

• 选择SQL Server 服务。

  

• 右键单击SQL Server并选择Properties。

• 在Startup Parameters下的打开窗口中，键入-T272并单击Add，然后按Apply按钮并重新启动。

  

回答by Lukasz Szozda

From SQL Server 2017+you could use ALTER DATABASE SCOPED CONFIGURATION:

从SQL Server 2017+你可以使用ALTER DATABASE SCOPED CONFIGURATION：

IDENTITY_CACHE = { ON | OFF }

Enables or disables identity cache at the database level. The default is ON. Identity caching is used to improve INSERT performance on tables with Identity columns. To avoid gaps in the values of the Identity column in cases where the server restarts unexpectedly or fails over to a secondary server, disable the IDENTITY_CACHE option.This option is similar to the existing SQL Server Trace Flag 272, except that it can be set at the database level rather than only at the server level.

(...)

G. Set IDENTITY_CACHE

This example disables the identity cache.

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ;

IDENTITY_CACHE = { ON | 离开 }

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

（……）

G. 设置 IDENTITY_CACHE

此示例禁用身份缓存。

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ;

回答by Jeyara

I know my answer might be late to the party. But i have solved in another way by adding a start up stored procedure in SQL Server 2012.

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

Create a following stored procedure in 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

Then add it in to Start up by using following syntax.

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

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

This is a good idea if you have few tables. but if you have to do for many tables, this method still works but not a good idea.

如果您的桌子很少，这是一个好主意。但是如果你必须为很多表做，这种方法仍然有效，但不是一个好主意。

回答by green_mystic_Orb

This is still a very common issue among many developers and applications regardless of size.

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

Unfortunately the suggestions above do not fix all scenarios, i.e. Shared hosting, you cannot rely on your host to set the -t272 startup parameter.

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

Also, if you have existing tables that use these identity columns for primary keys, it is a HUGE effort to drop those columns and recreate new ones to use the BS sequence workaround. The Sequence workaround is only good if you are designing the tables new from scratch in SQL 2012+

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

Bottom line is, if you are on Sql Server 2008R2, then STAY ON IT. Seriously, stay on it. Until Microsoft admits that they introduced a HUGE bug, which is still there even in Sql Server 2016, then we should not upgrade until they own it and FIX IT.

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

Microsoft straight up introduced a breaking change, i.e. they broke a working API that no longer works as designed, due to the fact that their system forgets their current identity on a restart. Cache or no cache, this is unacceptable, and the Microsoft developer by the name of Bryan needs to own it, instead of tell the world that it is "by design" and a "feature". Sure, the caching is a feature, but losing track of what the next identity should be, IS NOT A FEATURE. It's a fricken BUG!!!

微软直接引入了一个突破性的变化，即他们破坏了一个不再按设计工作的 API，因为他们的系统在重新启动时忘记了他们当前的身份。缓存或不缓存，这是不可接受的，名为 Bryan 的 Microsoft 开发人员需要拥有它，而不是告诉全世界它是“设计使然”和“功能”。当然，缓存是一个功能，但不知道下一个身份应该是什么，这不是一个功能。这是一个该死的BUG！！！

I will share the workaround that I used, because My DB's are on Shared Hosting servers, also, I am not dropping and recreating my Primary Key columns, that would be a huge PITA.

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

Instead, this is my shameful hack (but not as shameful as this POS bug that microsoft has introduced).

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

Hack/Fix:

破解/修复：

Before your insert commands, just reseed your identity before each insert. This fix is only recommended if you don't have admin control over your Sql Server instance, otherwise I suggest reseeding on restart of server.

在您插入命令之前，只需在每次插入之前重新设定您的身份。仅当您对 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)

Just those 3 lines immediately before your insert, and you should be good to go. It really won't affect performance that much, i.e. it will be unnoticeable.

就在插入之前的那 3 行，你应该很高兴。它真的不会对性能产生太大影响，即它不会引起注意。

Goodluck.

祝你好运。

回答by Sebastian Meine

There are many possible reasons for jumping identity values. They range from rolled back inserts to identity management for replication. What is causing this in your case I can't tell without spending some time in your system.

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

You should know however, that in no case you can assume an identity column to be contiguos. There are just too many things that can cause gaps.

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

You can find a little more information about this here: http://sqlity.net/en/792/the-gap-in-the-identity-value-sequence/

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