Have you ever noticed unexpected gaps in sequences in 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.

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

CREATE TABLE #idtest
(
      theID INT IDENTITY(1,1)
      , theValue VARCHAR(20) NULL
)

INSERT #idtest
VALUES ('Plane')
      , ('Train')
      , ('Automobile')

Now we’ll check the identity value by running:

DBCC CHECKIDENT(#idtest)

Which should return 3, the current identity value of the table.  Next, we’ll start a transaction, insert a few rows, and the roll back our changes.

BEGIN TRAN 

INSERT #idtest
VALUES ('Hop')
      , ('Skip')
      , ('Jump')

ROLLBACK TRAN

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.  Just an FYI, you can reset the identity value using the same DBCC command listed above, but you should make sure you understand the potential effects of doing so before you roll it out to your production systems.