March 21, 2012 at 8:35 am
In testing with auto increment I havde noticed the ID field gets incremented even when the insert fails resulting in missing entries. I have tested on 2 build levels of SQL 2005 with bad input and got the same result. Is this working as advertised or is there a way for the incfrement only to occur on a successful insert.
March 21, 2012 at 8:44 am
yeah that's working by design;it's considered the normal behavior to throw away the identities that get rolled back in a transaciton or deleted; they are not reused.
the identity() columns are just providing a unique integer, so they shouldn't have any business logic agaisnt them, like no gaps allowed or things like that.
Lowell
March 21, 2012 at 8:46 am
That's per-design.
If you need sequential numbers for users, have that done in the presentation layer of the application, or at least in the query (using Row_Number, most likely). Don't use identity (auto-increment) columns for that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 1, 2012 at 2:26 pm
SPAM reported.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 2, 2012 at 9:10 am
jamesforest (3/21/2012)
In testing with auto increment I havde noticed the ID field gets incremented even when the insert fails resulting in missing entries. I have tested on 2 build levels of SQL 2005 with bad input and got the same result. Is this working as advertised or is there a way for the incfrement only to occur on a successful insert.
If SQL Server re-used identity values that were previously rolled back in a transaction, it would wreak havoc for guys who use READ UNCOMMITTED isolation level or NOLOCK hints on their queries.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 2, 2012 at 9:18 am
Eric M Russell (4/2/2012)
jamesforest (3/21/2012)
In testing with auto increment I havde noticed the ID field gets incremented even when the insert fails resulting in missing entries. I have tested on 2 build levels of SQL 2005 with bad input and got the same result. Is this working as advertised or is there a way for the incfrement only to occur on a successful insert.If SQL Server re-used identity values that were previously rolled back in a transaction, it would wreak havoc for guys who use READ UNCOMMITTED isolation level or NOLOCK hints on their queries.
I don't think I follow you on this one Eric.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 2, 2012 at 9:33 am
Sean Lange (4/2/2012)
Eric M Russell (4/2/2012)
jamesforest (3/21/2012)
In testing with auto increment I havde noticed the ID field gets incremented even when the insert fails resulting in missing entries. I have tested on 2 build levels of SQL 2005 with bad input and got the same result. Is this working as advertised or is there a way for the incfrement only to occur on a successful insert.If SQL Server re-used identity values that were previously rolled back in a transaction, it would wreak havoc for guys who use READ UNCOMMITTED isolation level or NOLOCK hints on their queries.
I don't think I follow you on this one Eric.
It's problematic enough when one SPID (using NOLOCK) queries the ID for another SPID's half-baked uncommitted transaction. But when the transaction is rolled back, and then the same ID is recycled for another separte transaction, the potential for inconsistent results is even greater.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 2, 2012 at 9:45 am
Ahh I see what you are saying. Chalk up one more reason not to use dirty reads. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 2, 2012 at 11:51 am
Jeff Moden (4/1/2012)
SPAM reported.
I must be missing something. What spam?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 2, 2012 at 11:54 am
GSquared (4/2/2012)
Jeff Moden (4/1/2012)
SPAM reported.I must be missing something. What spam?
There was a spam message in here at one point. After he reported it they took down that message. I have had that same happen a couple times.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 3, 2012 at 6:09 am
Sean Lange (4/2/2012)
GSquared (4/2/2012)
Jeff Moden (4/1/2012)
SPAM reported.I must be missing something. What spam?
There was a spam message in here at one point. After he reported it they took down that message. I have had that same happen a couple times.
Ah. Makes sense.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 4, 2012 at 6:47 am
I suspect that the reason the ID values cannot be rolled back is because in order to roll them back, it would have to reseed the identity value and other queries in process may have already generated ID values above that. So the best it could do is to only roll back if there were no other transactions using the table, and then the behavior would be inconsistent. They would rather have consistent behavior.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply