Auto increment column value is not part of transaction – DEVELOPPARADISE
25/06/2018

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 is the catch- transaction do not roll back the auto increment field.

This concept will help you to solve some of your probems 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

A brief description of how to use the article or code. The class names, the methods and properties, any tricks or tips.

Here i have 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 roleback the transaction so expectation is that auto increment column is also rollback
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 roleback the transaction so expectation is that auto increment column is also rollback
well here is the difference- auto imcrement 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 print of intrest is the identity column value after the transaction whether it commit or rollback.