SQL Server Identity Jumps

A feature that has the side-effect of sometimes causing jumps in identity numbers is "identity caching". The reason this is implemented in SQL 2012 and above is to improve the efficiency of generating new IDs for identity fields by incrementing the on-disk number by some amount (1000 for 'int' fields) and serving the next 1000 IDs directly from memory. The side-effect of this is that if SQL Server is restarted, it may start up using the pre-written number on disk, and there will seem to be a gap since the ID numbers that were cached in memory were never used.

We do not disable this behavior on our shared SQL Servers for the following reasons:

• SQL Server trace flags are only meant for debugging, and the only way to disable the behavior is with a trace flag (Not to mention that it is an undocumented trace flag). Since we need to run our shared SQL Servers in a supported configuration, using the trace flag is not a good option for us.

• The identity-caching feature help reduce disk-writes and improve SQL Server performance.

The following alternatives are available if sequential numbers are important:

• You can use the new 'Sequence' object that is also implemented in SQL 2012 and above. The sequence object allows you to disable caching. However, it will require some changes to your application's SQL and/or stored procedures to take advantage of it.

• You can generate the numbers using an algorithm within your application.

• We offer VPS servers with SQL Server. With your own VPS running SQL Server, you can make any changes you want, such as adding the trace flag.

• If the main goal is to reduce confusion for users when they see a gap, starting identity numbers higher (such as 10000) or displaying numbers with a minimum number of digits and leading zeros (such as 00001) can reduce the perceived difference when this occurs.

Added By: Davidd (talk) 10:30, 13 November 2015 (CST)