Why Rolling back inserts with Identity columns is a bad idea.

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.