SQL Server：主键任意自增

Question

我们正在运行 SQL Server 2012 SP1 x64 (11.0.3000.0)。

我有一个表，其中InvoiceId字段为自增、主键：

CREATE TABLE Orders(
    InvoiceId           bigint           IDENTITY(1001,1) NOT FOR REPLICATION,
    OrderId             varchar(8)       NOT NULL,
    ...  -- other fields removed for brevity
    CONSTRAINT [PK_ORDERS] PRIMARY KEY CLUSTERED (InvoiceId)
    ON [PRIMARY], 
)

可以通过类似以下的简单存储过程插入新行：

SET  XACT_ABORT ON
SET  NOCOUNT ON

BEGIN TRANSACTION
    INSERT INTO Orders(
          OrderId,
          ... -- other fields removed for brevity
        )
    VALUES  (
          @orderId,
          ...
        )              

    SELECT @newRowId = SCOPE_IDENTITY()
COMMIT TRANSACTION

上述的存储过程将新创建的行号（Orders.InvoiceId）返回给调用者。

这段代码一直正常工作，[InvoiceId] 从1001开始，并在每次插入后递增1。

我们的用户插入了大约130行数据。当 [InvoiceId] 到达1130时，下一次插入它的值跳到了11091！

以下是数据截图：

我对这里发生的事情感到困惑。为什么自动增量计数器突然跳过近10,000个点？

我们使用 [InvoiceId] 的值来生成条形码，因此我们希望该值保持在特定范围内，最好是连续的系列。

我查阅了 T-SQL 文档，但未能找到与我的问题相关的内容。这是自增字段的正常行为吗（任意填充）？

- masroore

---

"IDENTITY" 从未保证连续，但在2012年有一个已知问题，即在重新启动服务后可能会突然跳跃，导致大量间隔。 - Martin Smith

谢谢@MartinSmith！这似乎是我的问题所在。我记得在标识增量跳跃之前重新启动了数据库机器！我该如何避免这种情况？ - masroore

你无法完全避免这个问题，因为连续性从未得到保证，但是你可以设置一个跟踪标志来获取较慢（已记录）的 2008 行为，或者你可以使用一个缓存大小更小的序列。请参见此处的讨论。 - Martin Smith

使用 SEQUENCE 代替 IDENTITY。 - Aaron Bertrand

看起来问题已经被分析并且可以关闭了？ - Ray 'user1578904'

Answer 1

感谢 Marting & Aron，我已经找到了一个解决办法。以下是微软的官方回复：

在 SQL Server 2012 中，实现标识属性的方式已经更改以适应对其他功能的投资。在以前的 SQL Server 版本中，标识生成的跟踪依赖于为每个标识值生成的事务日志记录。在 SQL Server 2012 中，我们批量生成标识值，并仅记录批次的最大值。这减少了写入事务日志的信息量和频率，提高了插入的可扩展性。

如果您需要与以前的 SQL Server 版本相同的标识生成语义，则有两个选项可用：

• 使用跟踪标志 272 o 这将导致为每个生成的标识值生成一个日志记录。打开此跟踪标志可能会影响标识生成的性能。

• 使用一个设置为 NO CACHE 的序列生成器(http://msdn.microsoft.com/en-us/library/ff878091.aspx) o 这将导致为每个生成的序列值生成一个日志记录。请注意，使用 NO CACHE 可能会影响序列值生成的性能。

示例：

CREATE SEQUENCE s1 AS INT START WITH 1 NO CACHE; 
CREATE TABLE t1 (Id INT PRIMARY KEY DEFAULT NEXT VALUE FOR s1, col INT NOT NULL);

- masroore

Answer 2

或者，您可以使用专用表格来存储计数器。 这不是一个很好的设计模式，但它可以让您完全控制标识的工作方式。

- Dimon KOH