Transaction Rollbacks with Identity Columns

Have you ever noticed unexpected gaps in sequences in SQL Server identity columns? Even though you’ve got transactions set up for your inserts and a no-deletion policy, you find that there are missing values in what should be an unbroken sequence of numbers.  The problem could be partially related to transaction rollbacks.

Conventional wisdom would lead one to believe that a rolled back transaction would undo the logged data changes to a table. While a rollback will remove the data rows included in a transaction, it does not reset the identity value to its previous setting.  We can see this demonstrated in a brief example.

Transaction Rollbacks with Identity Columns

First, let’s create a table with an identity column and insert some data:

Now we’ll check the identity value by running DBCC CHECKIDENT(#idtest).

Which should return 3, the current identity value of the table. No surprises there.

Next, let’s change up that scenario. In this version, we will open a transaction, insert a few rows, and the roll back the transaction to undo the changes.

We just inserted three rows but rolled back the transaction, so the new rows were never committed. However, if you check the identity value again, you’ll see it’s been incremented to 6 even though no new rows have been committed to the table.

This is actually intended behavior and not a bug in the product. If you think through some concurrency scenarios, you can understand why identity columns would be handled in this manner.

As a side note, you can reset the identity value using the same DBCC command listed above (with slightly different arguments), but you should make sure you understand the potential effects of doing so before you roll it out to your production systems.

Conclusion

Transaction rollbacks with identity columns can produce some unexpected results. Knowing how identity sequences are handled in a rolled-back transaction will help avoid confusion.