TRUNCATE vs DELETE

Question

Hi

From background reading I understand that there are some fundamental differences between using TRUNCATE and DELETE with no WHERE clause. For instance, TRUNCATE is a DDL command and logged at the page allocation level, whereas DELETE is a DML command logged at the row level. But essentially both are logged and can be rolled back if required. So what are the material differences between using these 2 commands. So far, I can think of these:

• Performance (TRUNCATE is quicker)
• Re-seeding identity columns (only done by TRUNCATE)
• Deallocation of space used by the table (only done by TRUNCATE)
• Triggers not fired by TRUNCATE
• TRUNCATE not possible on tables referenced by a foreign key

For instance, if you used DELETE could you restore a database to a point in time where half the table was deleted and half wasn't (not that I can imagine a practical situation where you would ever want to do this)? Clearly this would be impossible if you used the TRUNCATE command. Also, as TRUNCATE is a DML command, is it still compatible with log shipping?

All of the above is assuming you are using SQL 2008 and a full recovery model.

Julia.

Answer 1

For instance, if you used DELETE could you restore a database to a point in time where half the table was deleted and half wasn't (not that I can imagine a practical situation where you would ever want to do this)? Clearly this would be impossible if you used the TRUNCATE command.

This would also be impossible with DELETE if it was issued as a single command in a single transaction because the transaction will either  be redone, or undone in its entirety.  The only way you could restore to a point in time where half the table existed is if you performed a the DELETE operation in small batches in separate transactions that committed between DELETE's.

Truncate is good if you need to whack an entire table in one shot because it only logs the page deallocations, which makes it compatible with log shipping.

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

Answer 2

Thanks for the reply, Jonathan.

The difference in logging between TRUNCATE and DELETE has always fascinated me (probably because I've never fully understood it). But from what you saying... all the extra logging that DELETE does (i.e. per row) offers zero benefit as you can't actually rollback/restore the data in a 'per row' way. Is that correct?

Answer 3

Julia, Did you ever find the answer to your question.

I too was wondering about the samething and don't quite understand what the benefit is of having the deleted information logged. I know there is something fundamental I am not understanding about the log file and what its function is....