T-SQL

  • Comments posted to this topic are about the item T-SQL

  • Nice simple question on the basics. Every one should get this right (I hope)

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Whoopps clicked too often ...

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Very simple and easy question 🙂

    ~ 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

  • bitbucket-25253 (10/10/2012)


    Nice simple question on the basics. Every one should get this right (I hope)

    I was hoping this too....Not every one got it correct!

    Correct answers: 75% (9)

    Incorrect answers: 25% (3)

    ~ 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

  • Lokesh Vij (10/10/2012)


    bitbucket-25253 (10/10/2012)


    Nice simple question on the basics. Every one should get this right (I hope)

    I was hoping this too....Not every one got it correct!

    Correct answers: 75% (9)

    Incorrect answers: 25% (3)

    The % of correct should be increased 😀

    nice and easy ..

    thanks for the question

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • Easy question for the Thursday...

    +1 :-):-P

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

  • Good One!


    Sujeet Singh

  • Good and easy!

  • This was removed by the editor as SPAM

  • demonfox (10/10/2012)


    Lokesh Vij (10/10/2012)


    bitbucket-25253 (10/10/2012)


    Nice simple question on the basics. Every one should get this right (I hope)

    I was hoping this too....Not every one got it correct!

    Correct answers: 75% (9)

    Incorrect answers: 25% (3)

    The % of correct should be increased 😀

    It should be ... but it isn't!

    As of now: ...

    Correct answers: 75% (152)

    Incorrect answers: 25% (52)

    Total attempts: 204

    I am absolutely stunned that so many people get this wrong!


    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/

  • Hugo Kornelis (10/11/2012)


    demonfox (10/10/2012)


    Lokesh Vij (10/10/2012)


    bitbucket-25253 (10/10/2012)


    Nice simple question on the basics. Every one should get this right (I hope)

    I was hoping this too....Not every one got it correct!

    Correct answers: 75% (9)

    Incorrect answers: 25% (3)

    The % of correct should be increased 😀

    It should be ... but it isn't!

    As of now: ...

    Correct answers: 75% (152)

    Incorrect answers: 25% (52)

    Total attempts: 204

    I am absolutely stunned that so many people get this wrong!

    And it's actually getting worse:

    Correct answers: 74% (177)

    Incorrect answers: 26% (63)

    It's amazing that so many people can get this wrong. Like Hugo, I'm stunned.

    Even if someone doesn't know the answer they could use just a little thinking and application of common sense to get the correct answer, because the consequences for concurrency and performance of requiring the seed to be rolled back would be intolerable.

    Tom

  • L' Eomot Inversé (10/11/2012)


    the consequences for concurrency and performance of requiring the seed to be rolled back would be intolerable.

    That's not even the biggest issue. Consider this scenario:

    Transaction 1 starts, inserts new row. Identity value used = 12345.

    Transaction 2 starts, inserts new row. Identity value used = 12346.

    Transaction 1 rolls back.

    Transaction 2 inserts two more rows. What identity values to expect?


    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/

  • Great question. Thanks for posting. The question should be easy, but apparently isn't for some.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Hugo Kornelis (10/11/2012)


    L' Eomot Inversé (10/11/2012)


    the consequences for concurrency and performance of requiring the seed to be rolled back would be intolerable.

    That's not even the biggest issue.

    It's big enough to kill the idea of restoring seeds, though.

    Consider this scenario:

    Transaction 1 starts, inserts new row. Identity value used = 12345.

    Transaction 2 starts, inserts new row. Identity value used = 12346.

    Transaction 1 rolls back.

    Transaction 2 inserts two more rows. What identity values to expect?

    That's an illustration of why we would have to do something quite unpleasant to resolve the issues with restoring the seed. Actually, it's an overcomplicated illustration: no need to consider further work in Transaction 2, it's bad enough if it just commits its single update. I don't believe there's a reasonable solution. There are at least two unreasonable solutions (which I'm sure would never be implemented):

    (i) Transaction 2 is blocked when it attempts to insert, and remains blocked until Transaction 1 commits or rolls back;

    (ii) Transaction 2 is aborted immediately when transaction 1 rolls back; If Transaction 1 doesn't roll back, Transaction 2 is not committed until Transaction 1 commits; if Transaction 2 tries to commit it blocks, waiting for transaction 1.; if Transaction 1 rolls back Transaction 2 is rolled back.

    In either case, we don't need an answer to your question - with (i) because Transaction 2's insert doesn't happen until after Transaction 1 commits or rolls back, with (ii) because Transaction 2 never gets to its second insert (it rolls back when Transaction 1 rolls back).

    Of course we would have to do some things with dbcc checkident too.

    (i) would be reasonably simple to implement (although a lock that blocks inserts but not updates would be new), (ii) rather less so (one can end up with an arbitrarily large group of transactions all of which must commit or roll back together).

    The consequences of implementing either (i) or (ii) in order to allow the seed to be restored are what I consider intolerable.

    Fortunately we don't have any of this nonsense - the seed is not part of the state that has to be restored by rollback.

    Tom

Viewing 15 posts - 1 through 15 (of 39 total)

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