Difference between DELETE and TRUNCATE in SQL Server

I found many people confused between delete and truncate in SQL Server when there is a need to delete all the records from a table. In this quick post I have tried to compile the difference between them. Have a look…

DELETE (Without Where condition)	TRUNCATE
1.Delete without where condition or any restriction deletes all the rows from a table but one at a time, so it deletes records through a  loop for all the rows.	1.Truncate deallocates data pages from the table, thus deleting all the rows at once. It doesn’t go for any loop and thus faster than delete.
2.Delete is DML (Handles table data)	2.Truncate is DDL (Handles Table)
3.Delete can be rollback.	3.Truncate can’t be rollback using log file, once the transaction is complete.
Explanation: Delete writes data to log file before committing but, Truncate doesn’t. Delete removes one row at a time and deleted row is stored in transaction log but, Truncate deallocates the data pages in a table and stores only this deallocation in the transaction log.
4.On Delete all delete triggers will be fired.	4.No trigger will be fired upon Truncate command.
5.Delete is slower as compared to Truncate.	5.Truncate is faster than Delete
6. Delete (without condition) doesn’t reset the identity column value, if present.	6. Truncate command resets the value to initial value or seed for identity column.
7. Delete command acquires table and/or page and row locks for the whole table.	7.Truncate acquires only table and page locks for the table. Since no row lock are used less memory is required.
8. Delete can leave empty pages in table since as page removal requires a table lock which doesn’t necessarily happen.	8.Truncate removes all pages. No empty pages are left behind in a table.

I hope this will help you …

Please comment if you have any question or, suggestion.

Thanks !!!