Unexpected jumps in identity column values - sometimes by thousands - can be alarming for developers and database administrators. This behaviour is common in SQL Server and is typically not a bug, but a result of performance optimizations introduced in newer versions.
What is an Identity Column?
An identity column in SQL Server automatically generates sequential numeric values for each new row. It is defined using:
CREATE TABLE Orders (
OrderID INT IDENTITY(1,1) PRIMARY KEY,
CustomerName VARCHAR(100)
);
IDENTITY(1,1)means the column starts at 1 and increments by 1.- SQL Server handles the value assignment internally.
Why Do Identity Values Jump?
Identity Value Caching
Starting with SQL Server 2012, Microsoft introduced identity value caching to improve performance. This means SQL Server pre-allocates a block of identity values in memory. If the server restarts or crashes, unused cached values are lost, resulting in a jump.
Common Triggers for Jumps
- Server Restart or Failover: Cached values are discarded.
- ROLLBACK of Transactions: Identity values are not reused.
- Parallel Inserts: High concurrency can cause gaps.
- Manual Inserts with
IDENTITY_INSERT: Can disrupt the sequence.
How to Control Identity Jumps
Option 1: Disable Identity Caching
In my opinion, if it’s not critical to the business logic, it’s better off left unchanged.
From SQL Server 2017 onward, you can disable identity caching:
ALTER DATABASE [YourDatabase] SET IDENTITY_CACHE = OFF;
This prevents jumps but may slightly impact performance.
Option 2: Use SEQUENCE Instead of IDENTITY
SEQUENCE objects offer more control and are not affected by caching:
CREATE SEQUENCE OrderSeq START WITH 1 INCREMENT BY 1;
Use it in inserts:
INSERT INTO Orders (OrderID, CustomerName)
VALUES (NEXT VALUE FOR OrderSeq, 'Alice');
Option 3: Monitor Identity Usage
Use DBCC CHECKIDENT to inspect and adjust identity values:
DBCC CHECKIDENT ('Orders', NORESEED);
ref: - asp.net - Identity column increment jump - Stack Overflow