Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Tim Mitchell

Tim Mitchell is a business intelligence consultant, author, trainer, and SQL Server MVP with over a decade of experience. Tim is the principal of Tyleris Data Solutions and is a Linchpin People teammate. Tim has spoken at international, regional, and local venues including the SQL PASS Summit, SQLBits, SQL Connections, SQL Saturday events, and various user groups and webcasts. He is a board member at the North Texas SQL Server User Group in the Dallas area. Tim 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 twitter.com/Tim_Mitchell.

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:

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.

Comments

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

Rob,

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.

Leave a Comment

Please register or log in to leave a comment.