predict the output

  • thava

    SSCrazy

    Points: 2275

    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

  • kapil_kk

    SSC-Insane

    Points: 21316

    Thanks for the question Thava ๐Ÿ™‚

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Lokesh Vij

    SSChampion

    Points: 10836

    Nice question Thava.. Thanks!

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • Mr. Kapsicum

    SSCertifiable

    Points: 6128

    easy one for the day ๐Ÿ™‚

  • Thomas Abraham

    SSChampion

    Points: 10761

    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

  • Carlo Romagnano

    SSC-Insane

    Points: 21793

    I remembered the double behaviour!

    ๐Ÿ˜€

  • sestell1

    SSChampion

    Points: 10230

    Good question!

    This little idiosyncrasy drove me nuts when I first ran into it.

  • thava

    SSCrazy

    Points: 2275

    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]

  • Revenant

    SSC-Forever

    Points: 42467

    Easy but very nice. Thanks, Thava!

  • NBSteve

    Hall of Fame

    Points: 3228

    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!

  • brandme

    SSC Rookie

    Points: 26

    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!

  • Hany Helmy

    SSChampion

    Points: 13321

    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

  • TomThomson

    SSC Guru

    Points: 104772

    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

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    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 20 total)

You must be logged in to reply to this topic. Login to reply