I talked a little recently in a blog about gaps in identity values. They can occur when you delete rows, and the identity process doesn’t “fill in” those gaps or otherwise manage the values to maintain a continuous sequence. Something similar happens with transactions, and it’s a behavior you might not expect.
If you were do to this:
CREATE TABLE MyTable
( id INT IDENTITY(1,1)
)
GO
INSERT mytable DEFAULT VALUES
INSERT mytable DEFAULT VALUES
INSERT mytable DEFAULT VALUES
INSERT mytable DEFAULT VALUES
INSERT mytable DEFAULT VALUES
BEGIN TRANSACTION
INSERT dbo.MyTable DEFAULT VALUES
INSERT dbo.MyTable DEFAULT VALUES
ROLLBACK
INSERT dbo.MyTable DEFAULT VALUES
GO
SELECT * FROM mytable
GO
What you get is something you might not expect:

In the script, 5 values were inserted, then a transaction started and 2 more values were inserted. The transaction was rolled back, so there are still 5 values in the table. We add another one and it gets an 8, not a 6.

Why not?

It’s a valid question, and it occurs for this reason. In the code above, the transaction starts and the next row inserts a new value (6) into the table. The identity process now increments it’s “next row” to 7. The next statement runs and it adds a new value (7) and the “next” counter is set to 8. When the rollback occurs, the counter is not changed and so the last INSERT adds a row with a value of 8.
This seems silly at first until you think about concurrency. Imagine that we run the code above to this point:
BEGIN TRANSACTION
INSERT dbo.MyTable DEFAULT VALUES
Now another user manages to insert a row into the table at this split second. They would actually insert a row with a value of 7, and the second INSERT in this transaction would insert an 8 for it’s value. If the rollback changed the counter, how do we then account for the row with a value of “7” that’s in the table? Inserts from another session are not a part of this transaction.
Resetting the counter could have drastic implications in a busy environment with lots of connections. It doesn’t make sense, and so this is why a transaction rollback doesn’t reset the identity value. You can try it yourself by running part of this code, then inserting a value in another session before the rollback.
Once again, the identity property for a table doesn’t manage sequence or gaps, and it is not a part of a transaction rollback. Don’t depend on specific values being set for the identity column in your tables.