Why is my SQL Server 2012 Identity Increment Jumping?

SQL Server 2012: Unexpected Identity Increment Behavior

This article addresses an issue in SQL Server 2012 where the identity increment for integer columns unexpectedly jumps to multiples of 100, instead of the expected increment of 1. This erratic behavior is often triggered by a system restart.

Root Cause:

The problem stems from a performance optimization in SQL Server 2012. It utilizes a cache to pre-allocate IDENTITY values. The default cache size is 1000.

Consequences:

Upon server restart, any unallocated values within this cache are discarded. This results in gaps in the identity sequence when new rows are inserted, as the database allocates new values from the refreshed cache.

Solutions and Mitigation Strategies:

While a completely gap-free sequence isn't guaranteed, several strategies can minimize the problem:

• Employ Sequences: Utilize SQL Server sequences with a smaller cache size (e.g., 100) and use NEXT VALUE FOR within a column default constraint.
• Implement Trace Flag 272: Activating trace flag 272 logs IDENTITY allocation events, enabling the detection and analysis of any gaps.
• Deactivate Identity Caching: Execute ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF; to disable caching for the affected database.

Important Consideration:

It's crucial to understand that none of these methods entirely prevent gaps in the identity sequence. For applications requiring absolute gapless identity values, alternative approaches like row timestamps or custom key generation mechanisms are recommended.

The above is the detailed content of Why is my SQL Server 2012 Identity Increment Jumping?. For more information, please follow other related articles on the PHP Chinese website!