SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Transaction Rollbacks with Identity Values

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:

      theID INT IDENTITY(1,1)
      , theValue VARCHAR(20) NULL

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

Now we’ll check the identity value by running:


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.


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


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.

Tim Mitchell

Tim Mitchell is a business intelligence consultant, author, trainer, and Microsoft Data Platform MVP with over thirteen years of data management experience. He is the founder and principal of Tyleris Data Solutions.

Tim has spoken at international and local events including the SQL PASS Summit, SQLBits, SQL Connections, along with dozens of tech fests, code camps, and SQL Saturday events. He is coauthor of the book SSIS Design Patterns, and is a contributing author on MVP Deep Dives 2.

You can visit his website and blog at TimMitchell.net or follow him on Twitter at @Tim_Mitchell.


Posted by Hal on 9 July 2009

You can also use this technique to set the next identity ID to a higher value.  Say you have a block of records where the identity is 123,456; but for some reason you want the next record to start at 200,000 you can use the following:

declare @Next_ID int

set @Next_ID = 200000

set identity_insert dbo.myUsers ON

begin tran myTran

insert dbo.myUsers ( UID )

values ( @Next_ID - 1 )

rollback tran myTran

set identity_insert dbo.myUsers OFF

Posted by paul.knibbs on 9 July 2009

Hal, as it says in the article, you can change the identity value using DBCC CHECKIDENT:

DBCC CHECKIDENT(dbo.myUsers, RESEED, 200000) would set the next identity value to 200,000 on the dbo.myUsers table.

Posted by Charles Kincaid on 9 July 2009

The reason for this is that the identity valuse are actually stored in the column meta-data.  See syscolumns or sys.columns.

When you do your inserts that got rolled back, or the insert failed (triggers, etc), the meta-data had already been updated.

Identity columns can generate duplicate entries.  You want to make sure they are unique then add a constraint.

Good post Tim.

Posted by Rob N. on 9 July 2009

A scenario where this works to your benefit is to have a single table that is responsible for issuing unique IDs that are actually inserted into a variety of tables. In effect, your auto-incrementing IDs are unique across this "set" of tables and guaranteed to not collide. Since the issuing table has no value in persisting the ID value records itself, you can use a pattern of BEGIN TRAN, INSERT 1, SELECT @MyNextID=SCOPE_IDENTITY(), ROLLBACK TRAN to get the ID values without filling up a table.

Posted by David Moloney on 9 July 2009


Just curious (and not to get too far off track but ...), is your use of rollback a matter of convenience or performance?

We simulate a sequence generator in our application doing something like you've described only we need to keep the "current" as well as the "next" value.  So when a new identity is generated, we delete the previous.  "Next" becomes "current".  "Next" becomes the new identity value.  Given that MSS won't support nested transactions, do you think we would gain efficiency using your roll back approach ?  In our case, "current" would be a savepoint rolled back (in place of deletion) and "next" would be left uncommitted ?

Posted by anu1krishna on 10 July 2009

Really Sounds an interesting topic to discuss

Posted by bobcathamy on 14 July 2009

I would consider this a bug.  Yhe metadata should be part of the transaction and get rolled back.

Interesting ways you have all come up with to use it as a feature, though.

Posted by raulysich2 on 16 June 2014

I don't think it is a bug because before the rollback there was data inserted in those rows. One thing is the insert operation and another is the rollback. Those slots should be marked as "used" and no other insert operation should be allowed within the context.

Note that the counters are reset when using TRUNCATE.

In addition, I tried the following to illustrate my point


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

INSERT INTO #idTest Values ('x'),('y'),('z')

DELETE FROM #idTest WHERE theValue IN ('x','y','z')


--Now Check the value of the identity


Leave a Comment

Please register or log in to leave a comment.