Truncate vs Delete in SQL Server

Difference between DELETE and TRUNCATE in SQL Server

Let’s discuss this point “Truncate vs Delete” below in simple way.

1. Delete is a DML statement whereas Truncate is a DDL statement.
2. Delete commmand deletes each row from the table which are satisfying the WHERE clause and logs it in the log file where as Truncate command deallocates the data pages and logs it in the log file.
3. Delete performs slower than truncate because of logging mechanism it has whereas Truncate is faster than delete.
4. Delete can remove full or partial data as per it’s filter defined in WHERE clause whereas Truncate does not support WHERE clause and all data will be removed from table.
5. Delete executes the Triggers on the table if it has any because it is a DML command whereas Truncate does not execute the Triggers.
6. Delete requires more locks than truncate because each row is locked for deletion in the table whereas Truncate requires less locks than delete because it locks the table (SCH-M) and page but not the individual rows.
7. Delete does not recover the space until it is used with at least an exclusive table lock in case of heap to deallocate the empty pages and in case of indexes the delete operation can leave empty pages, although these pages will be deallocated by a background cleanup process whereas Truncate claims to recover the spaces unless you use the REUSE STORAGE clause with Truncate.
8. Delete requires DELETE permission on table whereas Truncate minimum requires ALTER on table.
9. Delete works on tables which has foreign key references according to the configuration of foreign key whereas Truncate can not applied on tables which has foreign key references.
10. Delete does not reset the identity seed whereas Truncate resets the identity column seed value.
11. Delete can be rolled back as it logs each deleted row. Truncate is fully logged at the page level and could also be rolled back if you could catch it in time but it runs so fast that you won’t usually be able to. Both will rollback in implicit transactions that run into a error. Both can be easily rolled back inside an uncommitted explicit transaction, if needed. Just remember that rollbacks can take much longer than the original action, in some cases.
12. TRUNCATE can’t be used where transactional or merge replication is in place and it can’t be used on tables that are part of an indexed view.

Note:- When used with an explicit transaction Delete and truncate both can be rolled back.

Although i have tried to list all important points even if i have missed something important and will help this point of discussion, just put your points in comment section just below this post and i will update my post by mentioning your name with your suggested point.

Thanks!