January 30, 2019 at 8:08 pm
Comments posted to this topic are about the item Cleaning up the Identity
January 30, 2019 at 9:49 pm
Nice, easy one, thanks Steve
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
January 31, 2019 at 2:09 am
Nice QOTD Steve.
January 31, 2019 at 6:23 am
Good question Steve. Pretty shocked that at the time I posted this 12% of the answers were either 4 or "error". Seems that no matter how many times identity values and truncate get discussed, lots of people still don't understand it.
_______________________________________________________________
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/
January 31, 2019 at 9:59 am
Plenty of people may never have encountered this. Remember that learning is a journey for many. Few of us are in the same place as others.
January 31, 2019 at 10:58 am
Sean Lange - Thursday, January 31, 2019 6:23 AMGood question Steve. Pretty shocked that at the time I posted this 12% of the answers were either 4 or "error". Seems that no matter how many times identity values and truncate get discussed, lots of people still don't understand it.
In my defense, I didn't see the truncate before answering.
January 31, 2019 at 11:04 am
Note that the explanation is incorrect (at least for SQL 2016 and earlier, and most likely for SQL 2017 - the docs insist that the behavior changed after 2008 R2, but that is not correct). The deciding factor is not whether there are rows present in the table or not. The deciding factor is whether the table was truncated or simply had all rows deleted. Try running the exact same sequence, but instead of truncating the table, simply use DELETE FROM to remove all records. When truncating, Alice gets 0. When deleting all records, Alice gets 1.
To distinguish between those scenarios, use sys.identity_columns.last_value. If sys.identity_columns.last_value for the table is NULL, then RESEED with the value you want the first record to get. If sys.identity_columns.last_value for the table is not NULL, then reseed with the value one increment less than the value you want the first record to get.
January 31, 2019 at 12:09 pm
Steve Jones - SSC Editor - Thursday, January 31, 2019 9:59 AMPlenty of people may never have encountered this. Remember that learning is a journey for many. Few of us are in the same place as others.
I feel bad for those where learning is not a journey. Perhaps I a bit harsh in my assessment but it does seem that for some reason identity, transactions and truncate seem to consistently mess people up. As such it is good that we routinely revisit those concepts.
_______________________________________________________________
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/
January 31, 2019 at 12:36 pm
t.ovod-everett - Thursday, January 31, 2019 11:04 AMNote that the explanation is incorrect (at least for SQL 2016 and earlier, and most likely for SQL 2017 - the docs insist that the behavior changed after 2008 R2, but that is not correct). The deciding factor is not whether there are rows present in the table or not. The deciding factor is whether the table was truncated or simply had all rows deleted. Try running the exact same sequence, but instead of truncating the table, simply use DELETE FROM to remove all records. When truncating, Alice gets 0. When deleting all records, Alice gets 1.To distinguish between those scenarios, use sys.identity_columns.last_value. If sys.identity_columns.last_value for the table is NULL, then RESEED with the value you want the first record to get. If sys.identity_columns.last_value for the table is not NULL, then reseed with the value one increment less than the value you want the first record to get.
Yep, I was going to post a similar comment. I figured it would be easier to show in a longer format, so I just wrote it up as a blog post:
How Does DBCC CHECKIDENT Really Work When Resetting the Identity Seed (RESEED)?
The only thing I would change is that the deciding factor isn't just TRUNCATE TABLE vs DELETE, it also matters if no rows were ever inserted, in which case it behaves the same as when TRUNCATE TABLE is used.
And I tested on several version of SQL Server, including 2012, 2017 and 2019 CTP 2.2, and found no difference in behavior. So the documentation is wrong in two ways. I was about to edit it and post a PR.
Good to know about "sys.identity_columns.last_value". 🙂
Take care, Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
January 31, 2019 at 2:05 pm
Solomon Rutzky - Thursday, January 31, 2019 12:36 PMGood to know about "sys.identity_columns.last_value". 🙂
I originally found it via this Stack Overflow thread: https://stackoverflow.com/questions/472578/dbcc-checkident-sets-identity-to-0
February 4, 2019 at 4:06 am
nice question Steve and a valuable one to know/learn
Thanks
---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply