Sometimes, when you have a table with an IDENTITY column, there could be scenarios in which weird “gaps” are created between different IDs.

There can be several possible causes for this:

1. The most obvious cause is when rows are deleted from the table. If many rows are deleted from a table with an IDENTITY column, it’s obviously expected that nothing would “fill” up the “gaps” that these rows have left. IDENTITY values only go one way, they don’t automatically re-fill deleted values retroactively.

2. When a ROLLBACK is performed on a transaction after inserting into a table with an IDENTITY column, the increase in the IDENTITY value is NOT rolled back. So even if the row wasn’t actually inserted, the IDENTITY value is still increased. This can happen both with single-row INSERT commands, as well as BULK insertions. So if, for whatever reason, a lot of insertions are rolled-back in your database, you may see a lot of these “gaps”.

3. There’s a special mechanism, specifically in SQL Server 2012, which “pre-allocates” IDENTITY values for a table, and it does this in memory. So when the SQL service is restarted, next time you insert a value into the table, the IDENTITY value would “jump” by 1000 or 10000 (depending on the column data type). This happens in SQL 2012 only, and was reportedly fixed in later versions. More info about it in this blog post by Ahasan Habib. Continue reading Re-align Identity Last Value to Actual Max Value