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.
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
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.
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.
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.
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 ?
Really Sounds an interesting topic to discuss
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.
