Pros & Cons of TRUNCATE vs DELETE FROM

Question

Hey everyone,

Could someone give me a quick overview of the pros and cons of using the following two statements:

TRUNCATE TABLE dbo.MyTable

vs

DELETE FROM dbo.MyTable

It seems like they both do the same thing when all is said and done; but are there must be differences between the two.

Thanks again!

Answer 1

TRUNCATE doesn't generate any rollback data, which makes it lightning fast. It just deallocates the data pages used by the table.

However, if you are in a transaction and want the ability to "undo" this delete, you need to use DELETE FROM, which gives the ability to rollback.

Answer 2

truncate doesnt do any logging, delete does, so if you have a ton of records, your trans log is huge

Answer 3

Google is your friend:

http://www.google.com/search?sourceid=chrome&ie=UTF-8&q=truncate+vs+delete

You don't even need to go very far - the first result gives you a good comparison:

http://www.mssqltips.com/tip.asp?tip=1080

Answer 4

TRUNCATE TABLE doesn't log the transaction. That means it is lightning fast for large tables. The downside is that you can't undo the operation.

DELETE FROM logs each row that is being deleted in the transaction logs so the operation takes a while and causes your transaction logs to grow dramatically. The upside is that you can undo the operation if need be.

Answer 5

Another key point not mentioned in the other answers is that TRUNCATE TABLE will reset you identity to the initial seed, whereas DELETE FROM will carry on incrementing from where it left off.