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 🙂



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