sql server rollback...

Question

Possible Duplicate:
SQL Identity (autonumber) is Incremented Even with a Transaction Rollback

does a rollback also rollback identity values???

Answer 1

No, and it shouldn't...

The generation of Identities has to be outside of transactions or it wouldn't work.

Answer 2

a rollback does not reset the seed value (which means there will be a gap in the identity values).

example: If you insert 5 rows, whose identity values are:

5, 6, 7, 8, 9

and do a rollback, the next identity value will still be 10, even though 5 - 9 are missing.

Answer 3

No and you would not want it to reuse them. Identities should never be assumed to have no gaps (they aren't reused for deletes either).

Nor should the gaps matter to anyone. If someone asks for this functionality explain that it will require an extensive and expensive change to manually create the ids and that change will have a lot of risk of data integrity problems due to race conditions. Further this expensive and risky change will produce absolutely no gain whatsoever. The system will work slower (it takes longer to manually generate ids espcially if you are going to fill gaps) and there is a great risk that data may get misaligned if you reuse deleted ids and there are child records that were not deleted because someone else thought formally enforcing PK/FK relationships was a bad idea (or just missed a table). This is a requirement I personally would not accept. It is a too dangerous to the data.

Answer 4

No, and for good reason. Let's say there are two transactions active, each making use of the same identity. Say that transaction 1 grabs numbers (1, 3, 4, 7, 9) and transaction 2 grabs (2, 5, 6, 8). Transaction 2 is then rolled back. What should the identity roll back to? If the identity is reset so that the 'next' value is either the first or last value allocated by the transaction, the next program to grab a number from the identity would likely encounter a key violation of some variety when trying to use that number. For this reason identity number allocations are not transactioned.

Share and enjoy.