It wasn’t until the other day that I realised what the behaviour was when rolling back a transaction with an insert on a table with an identity column, to be fair I have never had a need to do so but I know that there are people who like to run code like below to validate before and after changes for updates for example:
BEGIN TRAN --Before Update SELECT ID, LogDate FROM [IdentityTest] WHERE LogDate <= DATEADD(DAY,-3,GETDATE()); UPDATE [IdentityTest] SET LogDate = GETDATE() WHERE LogDate <= DATEADD(DAY,-3,GETDATE()); --After Update SELECT ID, LogDate FROM [IdentityTest] WHERE LogDate <= DATEADD(DAY,-3,GETDATE()); --ROLLBACK TRAN --COMMIT TRAN
In the example above the user will see the data prior to the update and after the update and depending on whether they are happy with the results or not they either Commit or rollback.
This is certainly not a way in which I would recommend making updates,especially if you value concurrency as people tend to forget they have transactions open 🙂 .
As I say – This is just what I has seen people do and it was only the other day when I saw a similar situation but with an insert instead, The user believed that because the changes were made within a transaction this would rollback EVERYTHING however they did not consider the impact on the Identity column on the table they made the insert in.
Here is an example to demonstrate how a rollback on an insert will not rollback your identity seed on your table.
First I will create a demo table in my DBA database:
USE [DBA]; GO IF OBJECT_ID('dbo.IdentityTest') IS NULL BEGIN CREATE TABLE [IdentityTest]( ID INT IDENTITY(1,1), LogDate DATETIME ); END
Check the current Identity value and show that there is currently no data:
--Check current Ident SELECT IDENT_CURRENT('IdentityTest'); --Check data SELECT ID, LogDate FROM [IdentityTest];
So the current identity seed is 1 and we have no data , nothing unusual there.
Lets Insert a row inside of a transaction and rollback the change, to illustrate the before and after effects during the transaction I will include before and after sql statements too.
BEGIN TRAN --Before Insert SELECT ID, LogDate FROM [IdentityTest]; INSERT INTO [IdentityTest] (LogDate) VALUES (GETDATE()); --After Insert SELECT ID, LogDate FROM [IdentityTest]; ROLLBACK TRAN
We can see from the above screenshot that during the transaction there were no rows until we made the insert and then we rolled the transaction back, just to prove that the insert was rolled back here is the data now.
SELECT ID, LogDate FROM [IdentityTest];
No rows as expected, so what about the Identity lets check to see if that rolled back too.
--Check current Ident SELECT IDENT_CURRENT('IdentityTest');
Result: 1 so on the face of it this looks sound! lets try making an actual insert this time then immediately check IDENT_CURRENT and the data in the table.
--Make an insert INSERT INTO [IdentityTest] (LogDate) VALUES (GETDATE()); --Check current Ident SELECT SCOPE_IDENTITY(); --check the data SELECT ID, LogDate FROM [IdentityTest];
Interesting! this inserted ID 2 even though ID 1 does not exist and we thought we rolled it back!
So lets test this out by spinning through a bunch of inserts and rolling back all the even iterations, I have dropped and recreated the table for this test.
DECLARE @Iteration INT = 1 WHILE @Iteration < 50 BEGIN BEGIN TRAN INSERT INTO [IdentityTest] (LogDate) VALUES (GETDATE()); IF @Iteration%2 = 0 BEGIN ROLLBACK TRAN END ELSE BEGIN COMMIT TRAN END SET @Iteration += 1; END SELECT ID, LogDate FROM [IdentityTest];
The first thing I would assume if I saw gaps in the ID like this would be that someone deleted some data but I would never have even considered that its possible to cause the same gaps by rolling back an insert.
Whilst I did say in the title that its a bad idea to rollback inserts on tables with identity columns that’s only really if the Identity column actually has a real use other than just to make rows unique, in some cases the gaps may not even have an impact but certainly something to be aware of 🙂
Thanks for reading.