There can be gaps in the auto incrementing values implemented using IDENTITY property in SQL Server. In most practical situations this column is used as a surrogate key and not visible to the user. However in some business scenarios, the auto generated values have to be made visible to the users. This blog post is relevant to the latter case.
The implementation of IDENTITY property has been changed in SQL Server 2012 compared to the prior versions. In SQL Server 2012, the IDENTITY property is implemented using the new SEQUENCE object. By default the SEQUENCE object has a mechanism called caching, in which consecutive values are kept in memory for further use based on a threshold value. For example, when you insert first row and the current sequence value is 1, SQL Server may keep the next 1000 consecutive values in memory and keep the maximum value (i.e. 1000) in metadata. Once the incrementing value reaches 1000, the cache will create for numbers from 1001 to 2000 (note that these numbers are arbitrary). This is a performance improvement mechanism where the previous SQL Server versions keep the last value in transaction log.
The problem arises when there is a database failure or server restarts. Since the server is keeping only the maximum value, server will create a cache from this value. For example, you have inserted two rows and the current value for the auto increment column is 2. Now the server is restarted. You inserted the next row. Now the value of auto increment column will be 1001. This abrupt jump is not acceptable in some scenarios.
Microsoft has commented on this as ‘by design’ and suggested two workarounds.
- Create a SEQUENCE object in place of IDENTITY column using NO CACHE option
- Use trace flag 272 to force to use previous version’s IDENTITY property semantics