Understanding Unexpected Identity Increment Gaps in SQL Server 2012

SQL Server 2012 introduced sequences, altering how identity increments function. Unlike older identity mechanisms, sequences manage and allocate incremental values from a separate database object.

This offers advantages like improved concurrency and performance, especially under heavy load. However, it can also cause seemingly random jumps in identity values because sequences pre-allocate multiple values.

Solutions for Addressing Increment Gaps

Several strategies can help manage this behavior:

• Trace Flag 272: Activating trace flag 272 logs identity value allocations, helping to identify the frequency and patterns of these gaps.
• NO CACHE Sequence Generation: Creating sequences with the NO CACHE option forces the generation of only one identity value per insert. This restores the traditional, sequential increment behavior.

Factors Affecting Identity Value Allocation

Remember, these gaps aren't errors; they're a design change. Several factors influence how many identity values are allocated at once:

• Concurrent Users: The number of active users influences the batch size of allocated identity values.
• Server Resource Availability: System load and available resources affect batch size.
• Database Activity: Simultaneous inserts across multiple tables might lead to larger batch allocations.

By understanding the mechanics of sequences and using the suggested solutions, you can effectively minimize perceived jumps in identity increments and achieve the desired behavior in your SQL Server 2012 database.