Delete Vs Truncate difference on rollback

  • I guess anyone who has read even basic SQL will be aware of this classy question. And most of the people have fell for the catch on the difference based on rolling back the transaction.

    "Delete can be rolled back but Truncate cannot"

    Some time back i cleared it by experimenting that Truncate can also be rolled back, IF, it is contained inside the Transaction scope AND session is not closed.

    However my question is if the session is closed and we have full recovery model, then can we roll back the truncate from log ? I guess yes if the pages allocated for deletion by the truncate process are not yet overwritten by any other process (?). Please clarify.

  • If the transaction has committed, you can't roll it back, no matter whether it's a delete or a truncate. Committed transaction is committed.

    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
  • Totally agreed Gail. I have been to so many discussions over so many sites about this particular difference between DELETE & TRUNCATE that I'm confused.

    "DELETE can be ROLLED BACK but TRUNCATE cannot"

    What does it actually means and how ? using BEGIN TRAN... ROLLBACK, you can rollback both DELETE & TRUNCATE. and without that both will get committed and cannot be rolled back. So what I got was the only difference might be w.r.t. the restore process by logs i.e. restoring the backups with point in time recovery. But then also while recovering to a point, the data should remain same at any given point of time be it DELETE process or TRUNCATE process. May be i'm missing something or have landed in confusion zone.

  • sqlnaive (8/27/2013)


    Totally agreed Gail. I have been to so many discussions over so many sites about this particular difference between DELETE & TRUNCATE that I'm confused.

    "DELETE can be ROLLED BACK but TRUNCATE cannot"

    What does it actually means and how ? using BEGIN TRAN... ROLLBACK, you can rollback both DELETE & TRUNCATE. and without that both will get committed and cannot be rolled back. So what I got was the only difference might be w.r.t. the restore process by logs i.e. restoring the backups with point in time recovery. But then also while recovering to a point, the data should remain same at any given point of time be it DELETE process or TRUNCATE process. May be i'm missing something or have landed in confusion zone.

    It is one of those sql server myths that some people continue to preach. Either of those statements can be rolled back. When you read a post that states that a truncate cannot be rolled back you are reading false and incorrect information.

    _______________________________________________________________

    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/

  • Great... So the correct statement should be:

    "DELETE is a logged operation while TRUNCATE is not" as the truncate just deallocates the data pages from the table and that is the reason it's faster.

    The discussions arguing on delete can be rolled back and truncate not are useless.

    [Just want to get my understanding clear on this little subject]

  • Delete and truncate go deeper that that, they also have a major impact on things like Identity columns that need to be taken into account.

  • sqlnaive (8/27/2013)


    Great... So the correct statement should be:

    "DELETE is a logged operation while TRUNCATE is not" as the truncate just deallocates the data pages from the table and that is the reason it's faster.

    The discussions arguing on delete can be rolled back and truncate not are useless.

    [Just want to get my understanding clear on this little subject]

    They are BOTH fully logged operations. The amount of information written to the log is different. DELETE has to log all the data where a TRUNCATE only has to log the page deallocations.

    _______________________________________________________________

    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/

  • sqlnaive (8/27/2013)


    Totally agreed Gail. I have been to so many discussions over so many sites about this particular difference between DELETE & TRUNCATE that I'm confused.

    "DELETE can be ROLLED BACK but TRUNCATE cannot"

    What does it actually means and how ?

    It's a myth that's completely, 100% false. The only thing it means when someone says that is that they haven't tested it and don't understand.

    They can both be rolled back within a transaction. They can both be rolled forward during crash recovery or when restoring transaction logs.

    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
  • sqlnaive (8/27/2013)


    Great... So the correct statement should be:

    "DELETE is a logged operation while TRUNCATE is not" as the truncate just deallocates the data pages from the table and that is the reason it's faster.

    Nope. Completely false. They are both fully logged operations.

    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
  • Jason.Reeves (8/27/2013)


    Delete and truncate go deeper that that, they also have a major impact on things like Identity columns that need to be taken into account.

    I was taking only the logging difference into the account by this post. Thanks anyways for the reply Jason. 🙂

  • GilaMonster (8/27/2013)


    sqlnaive (8/27/2013)


    Great... So the correct statement should be:

    "DELETE is a logged operation while TRUNCATE is not" as the truncate just deallocates the data pages from the table and that is the reason it's faster.

    Nope. Completely false. They are both fully logged operations.

    Great... so it means the underlying difference is the degree of logging they do.

  • sqlnaive (8/27/2013)


    GilaMonster (8/27/2013)


    sqlnaive (8/27/2013)


    Great... So the correct statement should be:

    "DELETE is a logged operation while TRUNCATE is not" as the truncate just deallocates the data pages from the table and that is the reason it's faster.

    Nope. Completely false. They are both fully logged operations.

    Great... so it means the underlying difference is the degree of logging they do.

    Semantically speaking, it's a difference in what gets logged.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden (8/27/2013)


    Semantically speaking, it's a difference in what gets logged.

    Indeed. TRUNCATE is more like a DDL statement, it logs in the same way as a DROP TABLE does. DELETE is definitely a DML statement and behaves like one.

    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
  • sqlnaive (8/27/2013)


    GilaMonster (8/27/2013)


    sqlnaive (8/27/2013)


    Great... So the correct statement should be:

    "DELETE is a logged operation while TRUNCATE is not" as the truncate just deallocates the data pages from the table and that is the reason it's faster.

    Nope. Completely false. They are both fully logged operations.

    Great... so it means the underlying difference is the degree of logging they do.

    No, there are other differences too. If there are ON DELETE or INSTEAD OF DELETE triggers on the table, DELETE causes them to fire but TRUNCATE doesn't. If there is an identity column, TRUNCATE sets the current identity value back to the value specified in the table declaration but DELETE doesn't alter the current identity value. The amount of logging is different too, as DELETE logs row deletions and TRUNCATE logs page deletions. They also take different locks (I think TRUNCATE takes only a table lock, while DELETE takes row and sometimes also page locks). DELETE can make data pages in cache dirty, TRUNCATE can't. And so on.

    Tom

  • L' Eomot Inversé (8/27/2013)


    I think TRUNCATE takes only a table lock

    TRUNCATE take a schema modification lock

    DELETE takes row and sometimes also page locks

    Delete can take X locks at table, page or row level, depending how many rows are being deleted and a few other factors. It will also have IX locks at higher levels (if the X lock is page, there will be an IX table lock, if the X lock is row, there will be IX locks at page and table)

    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

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

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