Why Does My SQL Server 2012 Identity Column Increment by 100s After a Restart?

Abnormal growth problem of auto-increment column in SQL Server 2012

In SQL Server 2012, an unusual behavior was observed: the auto-increment value of the "ReceiptNo" column in the "Fee" table would intermittently jump by 100. This is inconsistent with the usual increment pattern of auto-increment columns.

Abnormal behavior details:

• The self-increment jump increases by 100, but the last three digits remain the same (e.g., 306).
• This behavior is triggered after the computer is restarted.

Reason:

This behavior is a result of performance improvements introduced in SQL Server 2012. By default, the database now caches 1000 values ​​when assigning an auto-increment value to an int column. After a restart, unused cached values ​​may be lost, causing gaps in the auto-increment sequence.

Document reference:

SQL Server documentation description:

"For performance reasons, SQL Server may cache auto-increment values, and during a database failure or server restart, some assigned values ​​may be lost... This may cause gaps in auto-increment values ​​during insertion."

Possible solutions:

If gaps in auto-increment columns are a concern, consider the following workarounds:

• Use SEQUENCE: Define a sequence with a smaller cache size and use "NEXT VALUE FOR" in the column default value.
• Enable trace flag 272: This will log allocations of self-increasing values, allowing you to see gaps.
• Disable auto-increasing caching: Perform "ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF" for a specific database.

Important Tips:

It’s important to understand that these solutions do not guarantee complete gap-free operation. Auto-incrementing columns by themselves do not guarantee a gap-free sequence due to failures or rolled-back transactions. For truly gapless values, consider other solutions such as a custom algorithm or an external key generator.

The above is the detailed content of Why Does My SQL Server 2012 Identity Column Increment by 100s After a Restart?. For more information, please follow other related articles on the PHP Chinese website!