Lots of inserts? Part II

  • Kenneth.Fisher

    SSCoach

    Points: 19587

    Comments posted to this topic are about the item Lots of inserts? Part II

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • HappyGeek

    SSCoach

    Points: 18666

    Good question thanks.

    ...

  • This was removed by the editor as SPAM

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Great question!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Ed Wagner

    SSC Guru

    Points: 286958

    Nice question to get the grey matter moving on a Monday morning. Simple and straightforward. Thanks.

  • TomThomson

    SSC Guru

    Points: 104772

    It's a nice question, but two things surprise me:

    (a) that something this fundamental about ROLLBACK and/or GO is considered advanced/hard/obscure enought to be worth 2 points.

    (b) that 55% of the first 338 answers were wrong.

    Maybe (b) indicates that whoever decided it was worth 2 points has a much clearer picture of how much people don't know about SQL Server's data engine than I have and I'm wrong to be surprised by either (a) or (b).

    Tom

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716284

    I decided it was worth 2 points because it's tricky and this could be confusing to people that conflate batches with transactions, or misunderstand how "GO n" works. It's tricky enough to confuse people.

  • Revenant

    SSC-Forever

    Points: 42467

    TomThomson (8/8/2016)


    . . . Maybe (b) indicates that whoever decided it was worth 2 points has a much clearer picture of how much people don't know about SQL Server's data engine than I have and I'm wrong to be surprised . . .

    Sure, Tom. I think you do not realize how much you know. A year ago I mentored a group of aspiring DBAs and it gave me a good insight into how much one has to know to be in that profession.

    And thanks to Kenneth for the question.

  • George Vobr

    SSCrazy Eights

    Points: 8995

    Nice question and a very good detailed explanation, thanks Kenneth. 🙂

    The key to the correct answer is the ROLLBACK.

    https://msdn.microsoft.com/en-us/library/ms181299.aspx

  • Sean Lange

    SSC Guru

    Points: 286471

    No discussion about nested transactions would be complete without a reminder that they are really a myth and do not do what it seems like a real nested transaction should. http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/[/url]

    _______________________________________________________________

    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/

  • TomThomson

    SSC Guru

    Points: 104772

    Sean Lange (8/9/2016)


    No discussion about nested transactions would be complete without a reminder that they are really a myth and do not do what it seems like a real nested transaction should. http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/[/url]

    The idea that SQL Server doesn't support nested transactions is the myth. When it does allow them the nest can have only one transaction at each level; only the outermost transaction uses "begin transaction" and "commit transaction" commands (unfortunately SQL Server doesn't enforce this, it allows meaningless begin transaction and commit transaction commands at levels other than the outermost, and this cause pointless confusion); inner transactions use "save transaction" with a mandatory name parameter to begin a transaction, and "rollback transaction <name>" to roll back the nest to the point immediately before the beginning of the named transaction; only the outermost transaction can be committed. To rollback the whole nest the rollback transaction command can either specify the name of the outermost transaction (specified in the begin transaction statement) or not provide a name, it doesn't matter which it uses as both mean the same.

    The idea of commiting an inner transaction is just plain bizarre, so perhaps people should apply a little common sense and realise that anything that uses "commit" for inner transactions is not a transaction nest, it's just a counter (and it's this same bizarreness of inner commit that precludes more than one subtransaction at the same inner level as the T-SQL language doesn't express any parellism).

    Perhaps things would have looked much cleaner if begin transaction had not been allowed in an in-transaction execution context, commit transaction had not allowed a (meaningless) name parameter, and the command for beginng an inner transaction had been named something like "begin subtransaction" instead of save transaction.

    So there are two myths here:

    Myth 1: using "begin transaction" inside a transaction creates a nested transaction

    Myth 2: SQL Server doesn't support nested transactions

    The big problem is that people keep on claiming that Myth 1 being a Myth means that Myth 2 is not a myth, and that's just plain silly.

    Tom

  • Kenneth.Fisher

    SSCoach

    Points: 19587

    Sean Lange (8/9/2016)


    No discussion about nested transactions would be complete without a reminder that they are really a myth and do not do what it seems like a real nested transaction should. http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/[/url]

    Absolutely! And I should have included that in the answer. Thanks for mentioning it.

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • SQLRNNR

    SSC Guru

    Points: 281210

    Steve Jones - SSC Editor (8/8/2016)


    I decided it was worth 2 points because it's tricky and this could be confusing to people that conflate batches with transactions, or misunderstand how "GO n" works. It's tricky enough to confuse people.

    I continue to see people that have never seen GO n, so I can see the scoring logic there.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 13 posts - 1 through 13 (of 13 total)

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