August 27, 2013 at 6:06 am
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.
August 27, 2013 at 6:25 am
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
August 27, 2013 at 7:00 am
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.
August 27, 2013 at 7:57 am
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/
August 27, 2013 at 8:40 am
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]
August 27, 2013 at 8:46 am
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.
August 27, 2013 at 8:50 am
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/
August 27, 2013 at 9:01 am
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
August 27, 2013 at 9:01 am
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
August 27, 2013 at 9:14 am
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. 🙂
August 27, 2013 at 9:16 am
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.
August 27, 2013 at 9:57 am
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
Change is inevitable... Change for the better is not.
August 27, 2013 at 10:37 am
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
August 27, 2013 at 11:08 am
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
August 27, 2013 at 11:13 am
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
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply