Why Identity Columns does not participate in transaction?

Question

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

Why are the linked to questions/answers not adequate? What more do you expect? — Damien_The_Unbeliever, Apr 03 '13 at 10:02
Sir, recently I heard that since the column value is maintained in some sytem table heceforth it cannot participate in transaction because they are not meant to do so(system table).Is it true? If so, then I want to know more about it? Or the information I gleaned is completely wrong? — priyanka.sarkar, Apr 03 '13 at 10:04
The *reason* it doesn't participate in the transaction is because a design decision was made that it would not participate in the transaction. Anything else about where it's stored, etc, is just incidental. — Damien_The_Unbeliever, Apr 03 '13 at 10:17
possible duplicate of [Why are there gaps in my IDENTITY column values?](http://stackoverflow.com/questions/14642013/why-are-there-gaps-in-my-identity-column-values) — Pondlife, Apr 03 '13 at 14:14

score 3 · Answer 1 · answered Apr 03 '13 at 10:22

3

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'...

answered Apr 03 '13 at 10:22

Remus Rusanu

288,378
40
442
569

Yes. This is much better than just citing documentation because it shows why the designers of SQL Server *had* to do it this way (apart from a table lock to serialize access...). – usr Apr 03 '13 at 10:26

András Ottó · Accepted Answer · 2013-04-03T10:30:35.737

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.

Why Identity Columns does not participate in transaction?

2 Answers2