Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
patshaw
Posting Yak Master
177 Posts |
 Posted - 2010-06-14 : 11:56:36
|
| Never noticed this before but just curious why it happens:I have an identity column with an increment of 1. The current max value in this column is 30. I issue an insert statement inside an explicit transaction which would set the new max value of the identity column to 31 but then I rollback the tran. I would expect this not to increment the identity value as it was rolled back but when I issue the insert again and commit the tran the identity value of the inserted row is 32. So, it seems that even though I rolled back the first insert the identity value was still incremented. Why is this? |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-06-14 : 12:04:19
|
| That is the way it is designed to work.CODO ERGO SUM |
 |
|
|
patshaw
Posting Yak Master
177 Posts |
Posted - 2010-06-14 : 12:14:06
|
| Yep, I'm not disputing that Michael, just curious why. I suppose it has to be that way to preserve the value. If an insert has not been commited and another insert commits in the meantime then they have to hold distinct values. When a transaction begins with an insert in it, the next available id is assigned to it and whether it commits or is rolled back the value is used exclusively for that transaction. Does that sound correct? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-06-14 : 12:23:00
|
| It’s really just about concurrency and scalability.SQL Server increments the IDENTITY value and moves on so that there is no overhead dealing with reusing values that do not get committed.CODO ERGO SUM |
|
|
|
|
|
|
IDENTITY - Still increases after ROLLBACK