Identity Columns and Rollbacks

Identity columns are a very commonly used feature within tables in SQL Server. Basically when specified as an identity a column will automatically increment by the specified value; so if we have an identity increment of 1 and insert 5 rows they will automatically be numbered 1 to 5.

One cautionary measure with identities is that they don’t reset themselves when rows are deleted. If we delete rows 4 and 5 the next row will still be populated as identity 6. That’s fine, but what happens if we rollback an insert.

Here’s a quick table with three columns one of them ID which is set as an identity with an increment of one.

CREATE TABLE [dbo].[IdentityRollback](

       [ID] [smallint] IDENTITY(1,1) NOT NULL,

       [FirstName] [varchar](50) NULL,

       [LastName] [varchar](50) NULL,

) ON [PRIMARY]

GO

Very simple and straightforward, let’s insert a row with the following:

INSERT INTO IdentityRollback (FirstName, LastName) VALUES ('David','Alcock')

Note that I haven’t inserted a value for ID, let’s make sure all is well in there.

SELECT * FROM IdentityRollback 

As expected the one row and the ID column has started at our first increment. Now we’ll add the same row but rollback the transaction:

BEGIN TRAN

 INSERT INTO IdentityRollback (FirstName, LastName) VALUES ('David','Alcock')

 

ROLLBACK TRAN

We can run the select statement again to make sure no row has been inserted:

SELECT * FROM IdentityRollback

Exactly the same! Which is good because it shows the transaction has rolled back exactly as intended. So what happens when we insert a brand new row, what will the ID column be?

I’ve always wanted to meet Tom Hanks so let’s put him in the table.

INSERT INTO IdentityRollback (FirstName, LastName) VALUES ('Tom','Hanks')

The same select again,

SELECT * FROM IdentityRollback

but this time…

Although the transaction was rolled back, it hasn’t reset the identity. That’s actually happened by design; if there were other transactions creating rows (and identities) then imagine having to wait to see if every identity was successfully committed or not. If you need to rely on a sequential column of integers then perhaps the identity functionality isn't going to be the best option for you.

For further reading Pinal Dave has a solution to the problem here