Posted in Development

SQL Server – Change in Identity() Property during transactions

Recently while working on a scenario I found that in case of transaction if you are doing insertion operation on a table and when you Rollback your transaction then identity value that is inserted to the table is not rolled back.

Let me demo you on this scenario using an example. First we will create a table with identity property defined on a column.



       ID INT IDENTITY(1,1),

       Name varchar(10)


After creation of a table let’s inserted some records into this table in a transaction.


Insert into Test_id(Name) Values(‘A’),(‘B’)

We can see that 2 rows are inserted into the table with identity values starting from 1 as defined in the table definition.


Without committing the transaction we will rollback the transaction and check that there are no rows present in the table.


Now, we will again insert the rows in the table and found that identity values that got inserted during the transaction didn’t rolled back and it will insert values from the next number.


So friends, in case of transaction if we have identity property defined on table and rollback the transaction then identity values will not rolled back. In case of temporary tables also this scenario will behave same as main table.

Have a happy learning 🙂




I am Kapil Singh Kumawat working on SQL Server since last 5 years. I am from Jaipur, Rajasthan, India and currently working in Cognizant Technology Solutions as SQL Server Developer. I have a good experience in writing queries, performance tuning, SSIS, SSAS, Power BI, Data migration and database designing. Apart from database I have interest in travelling,watching football and listening music. My blogs are also published on

Leave a Reply

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

You are commenting using your 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