Auto Increment Column Value is Not Part of Transaction

Introduction

Most of us think when we make some changes and rollback the transaction, everything is rolled back, but here's the catch - transaction does not roll back the auto increment field.

This concept will help you to solve some of your problems and will also help you to extract the required information in some cases like how many records were part of a particular transaction.

Using the Code

Here, I created a table Test with a primary key column and another auto increment column.

CREATE TABLE [dbo].[test](
    [id] [int] NOT NULL,
    [sno] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Test 1

begin transaction
insert into test(id)
values(1)
rollback transaction

GO

Expected Result

Since we have rolled back the transaction, so expectation is that auto increment column is also rolled back.
Well, here is the difference.

Test 2

insert into test(id)
values(1)
insert into test(id)
values(2)

GO

Expected Result

Since we have rolled back the transaction, the expectation is that auto increment column is also rolled back. Well, here is the difference - auto increment value is set to 1 after the transaction even it is rolled back.

select * from test
id   sno
1    2
2    3

Points of Interest

Hope this will help you while solving problems where transaction is involved and your point of interest is the identity column value after the transaction whether it is commit or rollback.