Identity ID And Rollback

I was curious what would happen with inserting a values into a row with an identity values and then rolling back the transaction. Would the identity value created in the rolled back transaction be discarded?
Here’s a script to create a table with an identity column, with a few inserts and a rollback.

CREATE TABLE #IdentityTest(RecordID int not null identity(1,1), DisplayName varchar(20) not null);

INSERT INTO #IdentityTest(DisplayName) VALUES ('Record1');
SELECT SCOPE_IDENTITY();

BEGIN TRANSACTION;
INSERT INTO #IdentityTest(DisplayName) VALUES ('Record2');
SELECT SCOPE_IDENTITY();
ROLLBACK;

BEGIN TRANSACTION;
INSERT INTO #IdentityTest(DisplayName) VALUES ('Record3');
SELECT SCOPE_IDENTITY();
COMMIT;

SELECT * FROM #IdentityTest;

We can see that ID value 2 is generated for the 2nd insert, the transaction is rolled back, and the ID value 3 is used for the next value. So this will leave a gap in the identity values, the engine won’t try to fill the gap. This makes sense, especially in an environment where many processes would be hitting this table at the same time.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: