Post reply

Truncate and Delete

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

  • August 25, 2011 at 2:20 am

    #1372985

    Truncate is logged. You answered "Delete is logged, truncate is not logged." and therefore you were wrong.

  • August 25, 2011 at 2:40 am

    #1372995

    Thanks for your reply Nils, I now realized that truncate is actually logged.

    Quoting from: http://www.sqlservercentral.com/articles/delete/61387/

    TRUNCATE logs the deallocation of the data pages in which the data exists. TRUNCATE is faster than DELETE due to the way TRUNCATE "removes" rows from the table. It won't log the deletion of each row; instead it logs the deallocation of the data pages of the table. The TRUNCATE statement removes the data by deallocating the data pages used to store the table data and records only the page deallocation in the transaction log. Actually, TRUNCATE does not remove data, but rather deallocates whole data pages and removes pointers to indexes. The data still exists until it is overwritten or the database is shrunk. This action does not require a lot of resources and is therefore very fast. It is a common mistake to think that TRUNCATE is not logged. This is wrong. The deallocation of the data pages is recorded in the log file. Therefore, "Books Online (BOL)" refers to TRUNCATE operations as "minimally logged" operations. You can use TRUNCATE within a transaction, and when this transaction is rolled-back, the data pages are reallocated again and the database is again in its original, consistent state.

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

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