CODEWITHSUNDEEP
sql-server
priyanka.sarkar
priyanka.sarkar

Reputation: 26498

Why Identity Columns does not participate in transaction?

Suppose I have the below 

Begin Tran

Insert Into tbl(name) values('name1');

Insert Into tbl(name) values('name2');


Rollback

End

The table tbl has an identity column (id) and a varchar column (name).

Now obviously the data will be rolled back.

When I again tried to insert the record, the Id column value is 3 and not 1!

Why? Why does not Identity column value participated in transaction? Where it is stored? etc.

Well I have read this as well as this but still I need a more depth information.

Thanks in advance

Upvotes: 2

Views: 95

Answers (2)

András Ottó
András Ottó

Reputation: 7695

You can check the MSDN itself:

... "Reuse of values – For a given identity property with specific seed/increment, the identity values are not reused by the engine. If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again. This can result in gaps when the subsequent identity values are generated. "

"These restrictions are part of the design in order to improve performance, and because they are acceptable in many common situations. If you cannot use identity values because of these restrictions, create a separate table holding a current value and manage access to the table and number assignment with your application."

And I think the simple reason is that the seed is not set back for that colum, because it is generated like:

Each new value is generated based on the current seed & increment

Changing the seed value could cause more chaos with concurrent inserts, and comibend with UNIQUE or PRIMARY KEY, and insert could "die" more easily, specially in a transaction heavy enviroment.

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294227

T1:                      |  T2
begin                    |  begin     
insert <-- gets ID 1     |
                         |    insert <-- gets ID 2
rollback                 |

Now only if you can answer what value should the next ID be after T1 rolls back if the id generation 'participates in the transaction'...

Upvotes: 3

Related Questions