Difference between Truncate and Delete in SQL:

Nov 18, 2012
Truncate Delete
Truncate is a DDL command. Delete is a DML command.
We can not specify filter in WHERE clause. We can specify filter in WHERE clause.
Truncate deletes all row from Table. Delete deletes row based on WHERE condition or can delete whole table.
Truncate is faster as compared to delete Delete is slower as compared to Truncate.
No log is maintained for Truncate for rows, Log is maintained for data pages. log is maintained for Delete for each row.
Truncate can not activate a trigger. Delete can activate trigger.
It resets the identity column. It does not resets the identity column.

Note :- Delete maintains a ROLLBACK tablespace which stores data which is deleted, so when you exceute ROLLBACK data is gets back. In case of TRUNCATE table does not maintain such ROLLBACK tablespace therefore it does not return data after ROLLBACK