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.

CREATE TABLE Test_id

(

       ID INT IDENTITY(1,1),

       Name varchar(10)

)

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

BEGIN TRAN

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.

tran_1

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

tran_2

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.

tran_3.jpg

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 🙂

 

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s