sql server - Pros & Cons of TRUNCATE vs DELETE FROM

Question

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.

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.

EDIT: Note that the above is incorrect for SQL Server (but it does apply to Oracle). In SQL Server, it is possible to rollback a truncate operation if you are inside a transaction and the transaction has not been committed. From a SQL Server perspective, one key difference between DELETE FROM and TRUNCATE is this: "The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. 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 log."

In other words, there is less logging during a TRUNCATE because only the page deallocations are recorded in the transaction log, whereas with a DELETE FROM each row deletion is recorded. That's one of the reasons TRUNCATE is lightning fast.

Note also from that MSDN link that you cannot truncate tables that are referenced by foreign key constraints, participate in an indexed view, or are published by using transactional replication or merge replication.

EDIT 2: Another key point is that TRUNCATE TABLE will reset your identity to the initial seed, whereas DELETE FROM will carry on incrementing from where it left off. Reference: Ben Robinson's answer.