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

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

Answer 2

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

Answer 3

The fundamental difference is in the way they are logged. DELETE and TRUNCATE are logged differently but both can be rolled back in exactly the same way. All operations that change data are logged. In SQL Server there is no such thing as a non-logged operation.

Answer 4

Outline of Delete Vs Truncate in SQL server

For Complete Article take after this connection: Delete Vs Truncate in SQL Server

/*Truncate - Syntax*/
TRUNCATE TABLE table_name

/*Delete - Syntax*/
DELETE FROM table_name
WHERE some_condition

Answer 5

Plus all the answers, another point to consider that Truncate will not trigger the delete trigger of the table, but delete statement will trigger the delete trigger of the table for each row.

Answer 6

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.