Problem
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:
- 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.
- 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”.
- 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.
Solution
While retroactively fixing your “gaps” may not be an easy (or even a recommended) task, it’s far more preferable to fix such “gaps” the moment you identify them at the end of your table. Let’s say, for example, that you know that you’ve just rolled back a huge insertion of new rows to your table. Or that you have SQL 2012 and have just restarted your instance, so you expect the 1000 or 10000 value jump. Or that maybe you’ve simply just deleted a whole bunch of rows from your table.
In either case, you expect the next inserted row to come with a significant IDENTITY gap.
Wouldn’t you want to, let’s call it, “re-align” your next IDENTITY value with the actual current maximum value you have in your table, and thus avoid that gap?
I personally have encountered this scenario several times. So, I wrote a script that automatically finds ALL tables with an IDENTITY column, which don’t have their next value aligned with their actual max value, and then generates a DBCC CHECKIDENTcommand to fix that discrepancy.
You can see it here:
You can use it freely for your own benefit.
Remarks
The script I provided uses the BIGINT data type when checking the max value, so it should cover the smaller integer data types as well (INT, SMALLINT and TINYINT). But other, non-integer types may not work as well.