SQL Server:主键的任意自增 [英] SQL Server: arbitrary auto-increment of primary key

问题描述

我们运行的是 SQL Server 2012 SP1 x64 (11.0.3000.0)

We're running SQL server 2012 SP1 x64 (11.0.3000.0)

我有下表，InvoiceId 字段作为自动递增的主键:

I have the following table with the InvoiceId field as the auto-incrementing, primary key:

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], 
)

通过如下简单的存储过程插入新行:

New rows are inserted though a simple stored procedure like the following:

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

上述 sproc 将新创建的 row-id (Orders.InvoiceId) 返回给调用者.

The above sproc returns the newly created row-id (Orders.InvoiceId) to the caller.

代码运行良好，[InvoiceId] 从 1001 开始，每次插入后递增 1.

The code was working perfectly, with [InvoiceId] starting from 1001 and incrementing by 1 for each successive inserts.

我们的用户插入了大约 130 行.[InvoiceId] 是 1130，然后在下一次插入时它的值跳到 11091！

Our users inserted about 130 rows. [InvoiceId] was at 1130, then on the next insert its value jumped to 11091!

这是数据截图:

我对这里刚刚发生的事情感到困惑.为什么汽车公司的计数器突然跳了近10000点?

I'm baffled as to what just happened here. Why did the auto-inc counter suddenly skip nearly 10,000 points?

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

We're using the value of [InvoiceId] to generate barcodes, so we'd prefer the value to remain in a specific range, preferably in a contiguous series.

我已经仔细阅读了 T-SQL 文档，但没有找到与我的问题相关的任何内容.这是身份字段的正常行为(任意填充)吗?

I've perused the T-SQL documentation but failed to find anything related to my issue. Is this the normal behavior (arbitrary population) of an identity field?

推荐答案

UPDATE 感谢 Martining &Aron，我找到了解决方法.以下是微软的官方回应:

UPDATE Thanks to Marting & Aron, I've found a work-around. Here's the official response from Microsoft:

在 SQL Server 2012 中，标识属性的实现已更改以适应对其他功能的投资.在以前版本的 SQL Server 中，标识生成的跟踪依赖于生成的每个标识值的事务日志记录.在 SQL Server 2012 中，我们批量生成标识值，并且只记录批处理的最大值.这减少了写入事务日志的信息量和频率，从而提高了插入可伸缩性.

In SQL Server 2012 the implementation of the identity property has been changed to accommodate investments into other features. In previous versions of SQL Server the tracking of identity generation relied on transaction log records for each identity value generated. In SQL Server 2012 we generate identity values in batches and log only the max value of the batch. This reduces the amount and frequency of information written to the transaction log improving insert scalability.

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

If you require the same identity generation semantics as previous versions of SQL Server there are two options available:

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

• Use trace flag 272 o This will cause a log record to be generated for each generated identity value. The performance of identity generation may be impacted by turning on this trace flag.

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

• Use a sequence generator with the NO CACHE setting(http://msdn.microsoft.com/en-us/library/ff878091.aspx) o This will cause a log record to be generated for each generated sequence value. Note that the performance of sequence value generation may be impacted by using 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);

这篇关于SQL Server:主键的任意自增的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！