10

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

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

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

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

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

Here's the data screenshot:

data

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

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.

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?

Stephen C
  • 698,415
  • 94
  • 811
  • 1,216
masroore
  • 9,668
  • 3
  • 23
  • 28
  • 5
    `IDENTITY` has never guaranteed contiguous but there is a known issue in 2012 where it can suddenly jump leaving large gaps after restarting the service. – Martin Smith Aug 26 '13 at 12:45
  • Thanks @MartinSmith! This seems to be the issue in my case. I remember rebooting the db machine before the identity increment jump! How may I avoid this? – masroore Aug 26 '13 at 12:47
  • 1
    You can't avoid it for certain as contiguity was never guaranteed but there is a trace flag you can set to get the slower (logged) 2008 behaviour or you can use a sequence with a smaller cache size. See [the discussion here](https://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity) – Martin Smith Aug 26 '13 at 12:48
  • Use SEQUENCE instead of IDENTITY. – Aaron Bertrand Aug 26 '13 at 12:48
  • 1
    It seem that the issue is analysed and the question could be closed? – Ray 'user1578904' Aug 27 '13 at 06:40

2 Answers2

3

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

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.

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

• 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.

• 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.

Example:

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
  • 9,668
  • 3
  • 23
  • 28
0

Alternatively, you can have a dedicated table with the counters. It is not a good design pattern but it put you in full control of the how the identity work.

Dimon KOH
  • 1