SQL Server 2012列标识增量在第7个条目时从6跳至1000+

Question

我在SQL Server 2012数据库中有一个奇怪的情况，auto identity int列没有正确递增。

假设我有一个表，使用int auto identity作为主键，它会间歇性地跳过递增，例如：

1、2、3、4、5、1004、1005

这种情况会在随机数量的表中以非常随机的时间发生，无法复制以查找任何趋势。

是什么导致了这种情况？ 有没有办法让它停止？

- Andy Clark

---

不要期望身份值是密集的。例如，插入一行时发生回滚会导致一个身份值被“消耗”，从而在数字中留下间隙。 - HABO

我知道，比如说如果插入一条失败的记录，下一个标识号码将会被使用并丢失，但当它跳过1000时，这让我有点担心。 - Andy Clark

如果您在单个语句中插入2000条记录，而第1000条失败了，那么您将从序列中失去1000条记录。我曾经在一次回滚的事务中一次性失去了10万条记录。要么这样，要么阻止所有插入操作，直到事务提交。 - Ben

这是什么鬼东西。我们也开始遇到同样的问题，它正在影响我们管理数据的方式。 - Allen King

虽然了解 Sql Server 2012 的工作方式非常有帮助，但这是一个真正的问题。提出的解决方案对我没有用，因为我无法访问托管的 Sql Server Express 服务器。我也不能简单地使用序列生成器，因为我需要连续的数字 - 比如发票号码，跳跃1000不可接受或增加多个表中的数字不起作用。此外，我可能会从 Microsoft Access 前端添加行，因此无法仅更新 INSERT 语句来处理此问题。我正在考虑在插入触发器后更新非主键。还有更好的想法吗？ - MicrosoftAccessPros.com

Answer 1

这是非常正常的。Microsoft在SQL Server 2012中添加了序列（sequences），而且还改变了标识键的生成方式。在此参考一下。

如果你想要旧的行为，可以：

1. 使用跟踪标志272 - 这将导致每个生成的标识值都生成一个日志记录。开启此跟踪标志可能会影响标识生成的性能。
2. 使用NO CACHE设置的序列生成器(http://msdn.microsoft.com/en-us/library/ff878091.aspx)

- Mithrandir

---

一切都很正常，直到你用尽了整数。 - Simon_Weaver

我有一个“传统”的标识列，增量设置为42。该列的值增加了41706（哎呀！），这恰好是993 * 42。 - Simon_Weaver

@Simon_Weaver 你多久重启一次你的SQL服务器？我已经运行了几个实例一年多了，序列没有出现任何问题。 - Mithrandir

我们从未重新启动服务器，但序列突然跳了！！ - JYousef

有了这样的“特性”，谁还需要“漏洞”呢？ - HerrimanCoder

Answer 2

我遇到了相同的问题，在 SQL Server 2012 找到以下错误报告。 如果仍然相关，请查看导致此问题的条件-那里还有一些解决方法（尽管没有试过）。 故障转移或重启导致标识重新种植

- yoosha

Answer 3

虽然跟踪标志272可能适用于许多情况，但它绝对不适用于托管的Sql Server Express安装。因此，我创建了一个身份表，并通过INSTEAD OF触发器使用它。我希望这能帮助其他人，或者给其他人改进我的解决方案的机会。最后一行允许返回最后添加的身份列。由于我通常使用它来添加单个行，因此这可用于返回单个插入行的标识。

身份表：

CREATE TABLE [dbo].[tblsysIdentities](
[intTableId] [int] NOT NULL,
[intIdentityLast] [int] NOT NULL,
[strTable] [varchar](100) NOT NULL,
[tsConcurrency] [timestamp] NULL,
CONSTRAINT [PK_tblsysIdentities] PRIMARY KEY CLUSTERED 
(
    [intTableId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,  ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

还有插入触发器：

-- INSERT --
IF OBJECT_ID ('dbo.trgtblsysTrackerMessagesIdentity', 'TR') IS NOT NULL
   DROP TRIGGER dbo.trgtblsysTrackerMessagesIdentity;
GO
CREATE TRIGGER trgtblsysTrackerMessagesIdentity
ON dbo.tblsysTrackerMessages
INSTEAD OF INSERT AS 
BEGIN
    DECLARE @intTrackerMessageId INT
    DECLARE @intRowCount INT

    SET @intRowCount = (SELECT COUNT(*) FROM INSERTED)

    SET @intTrackerMessageId = (SELECT intIdentityLast FROM tblsysIdentities WHERE intTableId=1)
    UPDATE tblsysIdentities SET intIdentityLast = @intTrackerMessageId + @intRowCount WHERE intTableId=1

    INSERT INTO tblsysTrackerMessages( 
    [intTrackerMessageId],
    [intTrackerId],
    [strMessage],
    [intTrackerMessageTypeId],
    [datCreated],
    [strCreatedBy])
    SELECT @intTrackerMessageId + ROW_NUMBER() OVER (ORDER BY [datCreated]) AS [intTrackerMessageId], 
    [intTrackerId],
   [strMessage],
   [intTrackerMessageTypeId],
   [datCreated],
   [strCreatedBy] FROM INSERTED;

   SELECT TOP 1 @intTrackerMessageId + @intRowCount FROM INSERTED;
END

- MicrosoftAccessPros.com