SQL Server 2012 – Fixing Unexpected Gaps in IDENTITY Column

identitysql serversql-server-2012

I'm trying to generate unique purchase order numbers that start at 1 and increment by 1. I have a PONumber table created using this script:

CREATE TABLE [dbo].[PONumbers]
(
  [PONumberPK] [int] IDENTITY(1,1) NOT NULL,
  [NewPONo] [bit] NOT NULL,
  [DateInserted] [datetime] NOT NULL DEFAULT GETDATE(),
  CONSTRAINT [PONumbersPK] PRIMARY KEY CLUSTERED ([PONumberPK] ASC)    
);

And a stored procedure created using this script:

CREATE PROCEDURE [dbo].[GetPONumber] 
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [dbo].[PONumbers]([NewPONo]) VALUES(1);
    SELECT SCOPE_IDENTITY() AS PONumber;
END

At the time of creation, this works fine. When the stored procedure runs, it starts at the desired number and increments by 1.

The strange thing is that, if I shut down or hibernate my computer, then the next time the procedure runs, the sequence has advanced by almost 1000.

See results below:

PO Numbers

You can see that the number jumped from 8 to 1002!

  • Why is this happening?
  • How do I ensure that numbers aren't skipped like that?
  • All I need is for SQL to generate numbers that are:
    • a) Guaranteed unique.
    • b) increment by the desired amount.

I admit I'm not a SQL expert. Do I misunderstand what SCOPE_IDENTITY() does? Should I be using a different approach? I looked into sequences in SQL 2012+, but Microsoft says that they are not guaranteed to be unique by default.

Best Answer

This is a known and expected issue - the way IDENTITY columns are managed by SQL Server has changed in SQL Server 2012 (some background); by default it will cache 1000 values and if you restart SQL Server, reboot the server, fail over, etc. it will have to throw out those 1000 values, because it won't have a reliable way to know how many of them were actually issued. This is documented here. There is a trace flag that changes this behavior such that every IDENTITY assignment is logged*, preventing those specific gaps (but not gaps from rollbacks or deletes); however, it is important to note that this can be quite costly in terms of performance, so I'm not even going to mention the specific trace flag here.

* (Personally, I think this is a technical problem that could be solved differently, but since I don't write the engine, I can't change that.)

To be clear about how IDENTITY and SEQUENCE work:

  • Neither is guaranteed to be unique (you need to enforce that at the table level, using a primary key or unique constraint)
  • Neither is guaranteed to be gapless (any rollback or delete, for example, will produce a gap, this specific problem notwithstanding)

Uniqueness is easy to enforce. Avoiding gaps is not. You need to determine how important it is for you to avoid these gaps (in theory, you should not care about gaps at all, since IDENTITY/SEQUENCE values should be meaningless surrogate keys). If it is very important, then you should not be using either implementation, but rather roll your own serializable sequence generator (see some ideas here, here and here) - just note that it will kill concurrency.

Lots of background on this "problem":

Related Question