Search code examples
sqlsql-serversql-server-2012primary-keyauto-increment

Why is the Table ID (PrimaryKey) not incrementing in a proper sequence in SQL Server?

I have encountered an issue with the Table ID (PrimaryKey) in SQL Server, as it is not increasing in the expected sequential order. Could you please provide insight into why this might be happening?

ID
---
1
2
3
4
1010
Solution

  • IDENTITY and SEQUENCE generation is optimized for speed. So SQL Sever only saves the last value generated to disk every 1000 values (by default). In case of an unplanned shutdown (or even a planned shutdown for SQL Server 2012) the sequence generation will show this 1000-value gap.

    You can disable the caching of values with a database option, or set Trace Flag 272. But writing every generated value to the database can limit the throughput of your application.