Post reply

Truncate and Delete

  • August 17, 2011 at 9:38 am

    #1369693

    Subjective Adapts (8/17/2011)

    Truncate

    Delete

    Faster in performance wise, because it doesn’t keep any logs

    Slower than truncate because, it keeps logs

    Rollback is not possible

    Rollback is possible

    Actually Truncate is logged. Also, Truncate can be rolled back.

    Here is proof.

    http://jasonbrimhall.info/2010/10/11/a-haunting-tsql-tuesday-tale/

    Make sure you read Paul Randals post that is linked in that article as well.

    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

  • August 17, 2011 at 9:47 am

    #1369700

    Toreador (8/17/2011)

    mbova407 (8/17/2011)

    TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.

    Emphasis mine 😉

    When truncating - Row actions are not logged. Page deallocations (which is what a truncate does) are logged.

    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

  • August 17, 2011 at 9:48 am

    #1369701

    Koen Verbeeck (8/17/2011)

    Easy one today, although it seems the 'truncate is not logged'-myth stills seems pretty persistent.

    Too persistent -it's like a nasty virus.

    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

  • August 17, 2011 at 9:58 am

    #1369711

    Great question. There was a recent SSC article by Prashant Pandey[/url] that did an excellent job explaining the differences. So glad I read that!

  • August 17, 2011 at 10:15 am

    #1369722

    great question and fun reading all the responses today... 😎

  • August 17, 2011 at 11:17 am

    #1369754

    Got it right because the "truncate does not log" myth has been beaten out of me by this site, too. I also believe that truncate CAN be rolled back because of a couple QOTDs on the topic. Am I wrong?

  • August 17, 2011 at 11:28 am

    #1369758

    wware (8/17/2011)

    I also believe that truncate CAN be rolled back because of a couple QOTDs on the topic. Am I wrong?

    Not wrong on that one - see the script posted earlier in this thread for one example

    -------------------------------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

  • August 17, 2011 at 11:44 am

    #1369766

    Kenneth Wymore (8/17/2011)

    Nice question. I knew better than to choose truncate as not being logged because of previous QOTD's and articles regarding this same thing. the truncate does not log myth has been beaten out of me by this site. Thanks SSC! Considering how many have answered incorrectly, it looks like this was a much needed question and that many have learned something today.

    Judging by some of the comments, some people who got it wrong are sure (despite being presented with dclear and unambiguous ocumentation) that they got it right. Obviously these people haven't learnt anything (maybe they are incapable of learning?),

    Tom

  • August 17, 2011 at 2:50 pm

    #1369864

    Thanks for the question..

  • August 18, 2011 at 2:08 am

    #1369969

    Thanks for the question. Straight forward with no smoke and mirrors 😀

  • August 22, 2011 at 7:35 am

    #1371401

    I have a question: how can you execute a T-SQL command if you are not logged

    or maybe a better way to address the question would be : What "is logged" means in "Delete is logged"?

    I got my answer right by reading the help page and answer elimination: for me, if I don't have the right permission (which is defined by your loggin), I can not remove any data from a table. But after reading the discussion, I think I did not get the definition of the words "is logged" correctly...

  • August 22, 2011 at 7:41 am

    #1371406

    Logging in this context is not about logins or permissions, it's about write operations that gets logged in the transaction log. Two completely different things.

  • August 22, 2011 at 9:55 am

    #1371505

    Back to basics... good:-) question

    ===========================================
    Better try and fail than not to try at all...

    Database Best Practices[/url]

    SQL Server Best Practices[/url]

  • August 22, 2011 at 3:37 pm

    #1371724

    Nils Gustav Stråbø (8/22/2011)

    Logging in this context is not about logins or permissions, it's about write operations that gets logged in the transaction log. Two completely different things.

    oye!:ermm:

    That means there is some commands that are NOT logged in the transaction log... which might be important to know for...? :crazy:

    OK... where can I have more basics informations on this "logged" command?

  • August 25, 2011 at 2:18 am

    #1372981

    I think the question had multiple correct replies.

    I answer:

    Delete is logged, truncate is not logged.

    Truncate resets the identity for a table, delete does not.

    Which I believe is also a correct answer, but I was told that was wrong.

Viewing 15 posts - 31 through 45 (of 53 total)

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