Why Do SQL Server 2012 Identity Columns Exhibit Sporadic Increment Jumps?

Exceptional increment jump in SQL Server 2012 auto-increment column

In SQL Server 2012, users may experience unexpected behavior with auto-incrementing int columns, where the increment jumps from a smaller value to a larger value.

Question:

Why does the increment of the identity column jump intermittently, skipping values ​​in the sequence?

Answer:

This behavior is caused by changes in identity key generation after the introduction of sequences in SQL Server 2012. Identity values ​​are now generated using sequence objects behind the scenes. These values ​​are cached to improve performance, which can sometimes cause gaps in the delta sequence.

Mitigation plan:

To revert to previous incremental behavior:

• Enable trace flag 272: This generates logging for each identity value generated, but may impact performance.
• Create the sequence generator with the NO CACHE setting: this will disable caching and ensure a consistent delta sequence. Remember to recreate the existing identity column with the new sequence.

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