DELETE FROM and TRUNCATE table functionality

There are two main commands used for deleting all the data from a table: TRUNCATE and DELETE FROM. Two of these achieve the same result; however there are significant differences between the two. There are advantages, limitations and consequences that you should consider to decide which one to use.

About TRUNCATE
 TRUNCATE command is faster because it removes all records in a table by deallocating the data pages used by the table, thus reduces the resource overhead of logging in the log and the number of acquired locks as well.

 It does not generate any UNDO information, does not fire DELETE triggers and does not record any information in the snapshot log.

 The only record of the truncation is the page deallocation, so the removed data cannot be restored.

 WHERE condition cannot be applied in TRUNCATE command.

 Auto commit and cannot roll back.

 It reset the IDENTITY value back to the SEED and the deallocated pages can be re-used immediately. If you want to retain the identity counter, use DELETE FROM command instead.

 This means if you have a table with an identity column and you have 20 rows with a seed value of 1, you last record will have the identity’s value as 20. After the table is truncated, the identity column will have the value of 1 when a new record is inserted. While for DELETE command, when a new record is inserted, the identity column will have a value of 21.

 This command cannot be used on tables that are referenced by foreign keys, or involved in replication or log shipping.

 If a TRUNCATE TABLE is issued against a table that are referenced by foreign key, an error is returned: Cannot truncate table ‘xxx’.


About DELETE FROM
 DELETE FROM command logging all rows in the transaction log, thus it consumes more database resources and locks.

 WHERE clause can be applied to narrow down the rows that need to be deleted.

 Not an auto commit and the removed data can be rolled back.

 To reset the IDENTITY value back to the seed, use DBCC CHECKIDENT command once you are done with the DELETE FROM command.

 When a table requires all records to be deleted and TRUNCATE command cannot be used due to against the rules, the following statement can be used to achieve the same result as TRUNCATE command:

DELETE FROM [table_name]
DBCC CHECKIDENT(“table_name”, RESEED, “reseed_value”)

Hope this article provides some clarify to you on this issue.