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),
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 🙂