Truncate and Delete

  • Comments posted to this topic are about the item Truncate and Delete

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

  • Good question for testing the basics.

    M&M

  • This was removed by the editor as SPAM

  • Thanks for the question 🙂

    It will be interesting to see how many still think that truncate is a non-logged operation.

  • I missed it because I also selected an option that said "TRUNCATE cannot be logged". Baffled to know this is otherwise 🙂

    Made 3 selections and thought QoTd would validate my selection since only 2 was required. That validation did not happen ... and thus, I saw the dreaded red X saying "Sorry, you were wrong!"

    Kwex.

  • Truncate

    Delete

    TRUNCATE is a DDL command

    DELETE is a DML command

    TRUNCATE TABLE always locks the table and page but not each row

    DELETE statement is executed using a row lock, each row in the table is locked for deletion

    Cannot use Where Condition

    We can specify filters in where clause

    It Removes all the data

    It deletes specified data if where condition exists.

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

    Delete activates a trigger because the operation are logged individually.

    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

    Drop all object’s statistics and marks like High Water Mark free extents and leave the object really empty with the first extent. zero pages are left in the table

    keeps object’s statistics and all allocated space. After a DELETE statement is executed, the table can still contain empty pages.

    TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction lo

    The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row

    If the table contains an identity column, the counter for that column is reset to the seed value that is defined for the column

    DELETE retain the identity

    Restrictions on using Truncate Statement

    1. Are referenced by a FOREIGN KEY constraint.

    2. Participate in an indexed view.

    3. Are published by using transactional replication or merge replication.

    Delete works at row level, thus row level constrains

  • This was removed by the editor as SPAM

  • Nils Gustav Stråbø (8/17/2011)


    Thanks for the question 🙂

    Yes, it's a good question.

    It will be interesting to see how many still think that truncate is a non-logged operation.

    Interesting and somewhat horrifying - up to now, 43% said delete was not logged, which is amazing considering how much discussion of that there has been on SQLServerCentral.com; and more tha 50% got it wrong that way or some other way. 6% chosing "delete resets ID, truncate does not" is crazy, I hope these guys never use identity columns because if they believe that nonsense they are going to make some really horrible mistakes, like thinking that if their table behaves like a stack IDs will always be contiguous, which can cause lots of lovely bugs.

    Tom

  • easy question!! and good question!


    [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!
  • rfr.ferrari (8/17/2011)


    easy question!!

    I would liked to have thought so - but sadly (and alarmingly) not so going by the right / wrong answers given - roughly 50/50 split

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

  • Subjective Adapts (8/17/2011)


    Rollback is not possible

    Rollback is possible

    That one at least is not true. I wondfer how many of your other statements are also wrong?

  • Good question. Thanks. Oh, I missed it - but it was still a good question. Helps if you read the entire reference you find before answering.

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

  • Tom.Thomson (8/17/2011)


    Interesting and somewhat horrifying - up to now, 43% said delete was not logged, which is amazing considering how much discussion of that there has been on SQLServerCentral.com; and more tha 50% got it wrong that way or some other way. 6% chosing "delete resets ID, truncate does not" is crazy, I hope these guys never use identity columns because if they believe that nonsense they are going to make some really horrible mistakes, like thinking that if their table behaves like a stack IDs will always be contiguous, which can cause lots of lovely bugs.

    I agree with you Tom!!!!

    the CONCEPT is in fault here!!!


    [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, thanks.

    http://brittcluff.blogspot.com/

  • Appreciate the question. I missed it because I couldn't decide on my second choice, and went with "truncate is not logged" assuming the author was making that common error (um, sorry Stuart). Didn't know the bit about resetting the identity value to the seed, so happy to get that wrong and learn something!

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

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

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