TRUNCATE TABLE and ROLLBACK TRAN

  • Very good ?

    M&M

  • Hugo Kornelis (2/6/2012)


    I wanted to comment that this question is superfluous, that everyone knows this. But.....

    At the time of writing:

    "Incorrect answers: 56% (52)"

    And 34% of them are for "you can never rollback a truncate table statement"

    .

    .

    .

    .

    .

    .

    Picks up jaw from floor.

    It's got better -

    Correct answers:42%(76)

    Incorrect answers:58%(107)

    Total attempts: 183

    The myth still abounds regrettably

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • The distribution of answers is frankly terrifying.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • ako58 (2/6/2012)


    CREATE TABLE t(id INT IDENTITY, i INT)

    BEGIN TRAN

    INSERT INTO t VALUES (4),(2),(3)

    SELECT * FROM t

    ROLLBACK TRAN

    SELECT * FROM t

    INSERT INTO t VALUES (4),(2),(3)

    SELECT * FROM t

    where is rollback?

    I don't get your question.

    There is no TRUNCATE TABLE in your query and the first INSERT is complety rolled back, since the final result has only 3 rows.

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

  • Given the distribution of answers, I'm considering a followup question - "does recovery model ever affect the ability to rollback transactions?" because it appears way too many people don't understand what recovery models do.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • great question!!!

    Thanks!


    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • GilaMonster (2/6/2012)


    Given the distribution of answers, I'm considering a followup question - "does recovery model ever affect the ability to rollback transactions?" because it appears way too many people don't understand what recovery models do.

    I agree!


    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • Good question, one that needs repeating (peferably with some variation) quite frequently since the pernicious myth that trucate table is not logged so can't be rolled back is still widespread, despite many valiant attempts to debunk it.

    I was quite appalled by the number of people who appear to think this depends on the recovery model, or on bulk logging option settings. I think Gail's idea (above) of some questions to bolster people's knowledge of what recovery models are and do is an excellent one.

    Tom

  • GilaMonster (2/6/2012)


    Given the distribution of answers, I'm considering a followup question - "does recovery model ever affect the ability to rollback transactions?" because it appears way too many people don't understand what recovery models do.

    +1

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

  • Koen Verbeeck (2/6/2012)


    GilaMonster (2/6/2012)


    Given the distribution of answers, I'm considering a followup question - "does recovery model ever affect the ability to rollback transactions?" because it appears way too many people don't understand what recovery models do.

    +1

    + 2 :hehe:



    Everything is awesome!

  • Is it bad form, if you create a QotD, to list your own article as a reference?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (2/6/2012)


    Is it bad form, if you create a QotD, to list your own article as a reference?

    If I were you, I would not give that a second thought 😉

    edited for missing "not" - sorry for any bad inference :blush:

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • GilaMonster (2/6/2012)


    Is it bad form, if you create a QotD, to list your own article as a reference?

    If it's a good article? No, not at all. I've done it in the past.

    That being said, I do always prefer an official MS reference. But if there isn't one, a third-party reference that is solid enough (read: based on other MS material, or equipped with enough code samples to prove that the statements are true) will do just fine.

    I don't know your article, but I do know you - and that's enough for me to assume that your article will meet the bar. Easily!


    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/

  • GilaMonster (2/6/2012)


    Is it bad form, if you create a QotD, to list your own article as a reference?

    Might not be bad form, but I can just image the list of those who whine and cry "give me my points".

    So then ask some one else to be the author of the QOD or QODs who then can site your article(s) as justification.

    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]

  • GilaMonster (2/6/2012)


    Is it bad form, if you create a QotD, to list your own article as a reference?

    It's probably better to find another one just so there's input from two different people but if it's the best reference (that can be found without a quick search) I would say go for it. No sense in wasting time when you have a good, accurate article that you can find quickly.

Viewing 15 posts - 16 through 30 (of 56 total)

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