predict the output
-
Comments posted to this topic are about the item predict the output
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
This was removed by the editor as SPAM
-
Thanks for the question Thava 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
Nice question Thava.. Thanks!
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
easy one for the day 🙂
-
Thanks to Thava for the question. It usually makes a good question to point out those areas in SQL Server where the actual behavior doesn't match the expected behavior.
(Edited to insert word "Server".)
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn -
I remembered the double behaviour!
😀
-
Good question!
This little idiosyncrasy drove me nuts when I first ran into it.
-
i agree with thomas
well in our concern we have faced a serious issue on this one that's why i posted it here, in our concern we usually deploy the empty backup to the customer and there we run a default procedure to populate the mandatory table detail, the same procedure also used for year end updation it deletes all the existing records and reset all the identity to 1, all goes well until that day
one of our client ask us to prepare the script for the database and ask to deploy it in his server, Every thing goes well, what make us stumped was this DBCC CHECKIDENT, when the user wants the default data, we run that procedure ,we usually start identity with 1, but there it start's with 0 and the night mare begins, then i do a little Google search but no help, well then i decide to read detail in msdn finally i found it and know why it happen, then we change the entire procedure with some little tweaks and also update the clients system (Tedious but a fatal error is gone)
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
Easy but very nice. Thanks, Thava!
-
Good question, but the explanation should really mention that DBCC CHECKIDENT has two different behaviors based on whether or not there have been prior (non-truncated) rows present. I know that the link explains it all, but the given explanation basically comes down to "Read Books Online," which applies to every question. A good explanation should at least summarize the objective of the question. Thanks for the question though!
-
thank you!!
I didn't know the RESEED's double action.
only when after creation or truncating table, new_reseed_value is used. I got it!
-
NBSteve (12/26/2013)
Good question, but the explanation should really mention that DBCC CHECKIDENT has two different behaviors based on whether or not there have been prior (non-truncated) rows present. I know that the link explains it all, but the given explanation basically comes down to "Read Books Online," which applies to every question. A good explanation should at least summarize the objective of the question. Thanks for the question though!Agree +1
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant -
Good question.
The delete in the second case almost made me get it wrong, because deleting all the rows doesn't have the same effect as truncating the table of never inserting any rows; but it felt wrong, and I stared it until I realised that that delete wasn't deleting anythig, because no rows had ever been inserted, so the new value wouldnt be incremented to get the identity on first use.
I always wonder why SQL Server behaves this way, though; it seems pretty bizarre, and doesn't seem useful.
Tom
-
L' Eomot Inversé (12/29/2013)
I always wonder why SQL Server behaves this way, though; it seems pretty bizarre, and doesn't seem useful.My guess - one day, someone discovered that the way the code for IDENTITY was written happened to cause this behaviour. And then they decided not to fix it, but to describe it. Probable reasons for this decision:
1. Maybe the IDENTITY code is not nice modularized, but spread out throughout the other code, and changing it is hard and risky. Too hard and risky.
2. Maybe MS realized that the intended use of IDENTITY is to generate numbers, and hence decided not to put any effort in influencing which numbers are generated.
3. Maybe MS found out that, despite what I just wrote above, some important customer actually has code that relies on this behaviour, and they didn't want to tick off this customer by changing it.
It's probably a combination of at least two of the above.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply