Why Does My SQL Server Identity Increment Jump After Restarts?

Identity increment exception in SQL Server database

In the SQL Server database, the Identity increment of the "ReceiptNo" column of the "Fee" table exhibits inconsistent behavior, with the increment value jumping after a restart.

This abnormal phenomenon is mainly reflected in the following three scenarios:

1. If "ReceiptNo" is 1205446, jump to 1206306.
2. If "ReceiptNo" is 1206321, jump to 1207306.
3. If "ReceiptNo" is 1207314, jump to 1208306.

It’s worth noting that the last three digits (306) remain the same during these jumps. Additionally, this issue only occurs after the computer restarts.

Cause analysis:

This behavior is a direct result of performance optimizations introduced in SQL Server 2012. The database now uses a cache size of 1000 when assigning IDENTITY values ​​to integer columns. When the service is restarted, unused values ​​in the cache will be lost.

This subtlety is documented in the SQL Server documentation, which states that gaps in Identity values ​​may occur due to caching and may cause problems during database failures or server restarts.

Solution:

If such gaps are undesirable, consider the following workarounds:

1. Use the SEQUENCE generator with the NOCACHE option.
2. Enable trace flag 272 to log IDENTITY assignments, emulating pre-2008 R2 behavior.
3. Use "ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF" to disable Identity caching for a specific database.

Important Note:

It’s important to note that none of these solutions are guaranteed to be completely gap-free. Identity increments inherently run the risk of gaps due to concurrent transactions and potential insert failures. For a truly gapless solution, other approaches must be taken.

The above is the detailed content of Why Does My SQL Server Identity Increment Jump After Restarts?. For more information, please follow other related articles on the PHP Chinese website!