What happens to an Identity column value when an INSERT is rolled back in SQL?

What happens to an Identity column in a table when an INSERT statement fails and is rolled back before it is committed in SQL Server?  I came across this question while playing around with some unit tests in SQL and looking at how the unit testing framework rolled back transactions during teardown.  In a unit test, I was using an explicit Id value in a WHERE clause – say WHERE Id = 2 – and was not getting a result.

To see exactly what happens with the identity column, we can walk through a few simple transactions.

--Create table with an Identity column seeded to 1
CREATE TABLE IdentityRollbackTest (
Id int IDENTITY(1,1)
,SomeText varchar(10)
,MoreText varchar(50)
)
--Insert first record
INSERT INTO IdentityRollbackTest (SomeText,MoreText)
VALUES ('First','Identity rollback test row')
--Check that row was inserted
SELECT *
FROM IdentityRollbackTest

First we create the table, IdentityRollbackTest, INSERT one row, and check the results.  As expected there is one row, Id = 1, in the table.

And DBCC CHECKIDENT (IdentityRollbackTest) returns the below.  Remember that DBCC CHECKIDENT  returns the current, eg: last used, identity value – in this case, 1.

Now lets try to INSERT a second row, but rollback this transaction.


--Try to insert a second row, but rollback the transaction
BEGIN TRAN
INSERT INTO IdentityRollbackTest (SomeText,MoreText)
VALUES ('Second','Identity rollback test row')
ROLLBACK TRAN
--Check that second row was correctly rolled back
SELECT *
FROM IdentityRollbackTest

As expected, we still only get 1 row returned from the SELECT *.

BUT, this time when we run DBCC CHECKIDENT(IdentityRollbackTest)

We get a current identity value = 2!

Now to confirm what happened, we run a third INSERT statement, which we commit.


--Try to insert a third row, allow this one to commit
INSERT INTO IdentityRollbackTest (SomeText,MoreText)
VALUES ('Third','Identity rollback test row')
--Check that the third insert attempt did succeed, and that the Id column value is 3. This confirms that the identity value was
--incremented even though the second row insert was rolled back
SELECT *
FROM IdentityRollbackTest

When we SELECT * FROM IdentityRollbackTest this time, we see the Id has in fact incremented to 3, but we only have the two rows from the two statements we allowed to commit.

Why does this happen?

At first this behavior had me confused – why did the ROLLBACK not also rollback the incrementing of the Identity column?  Until I did a bit of googling and found the explanation – https://stackoverflow.com/questions/282451/sql-identity-autonumber-is-incremented-even-with-a-transaction-rollback .  Since the identity column’s only requirement is to be unique, not sequential, if transaction A starts, then transaction B starts, B can’t wait to see if A commits before “claiming” its own identity value.  Once an INSERT command has been issued, an identity value is “claimed” by a transaction, even if the transaction is never committed.  If the transaction never commits, that identity value is “lost” for good.

It’s a feature, not a bug.

2 responses to “What happens to an Identity column value when an INSERT is rolled back in SQL?

  1. Mark Wojciechowicz

    Nice post! This is a good one to point out and a common problem in testing.
    A couple ways to avoid this are using truncate, which resets the identity or fake table. You’ve inspired another post out of me 😉

Leave a Reply

Your email address will not be published. Required fields are marked *