Difference between Delete and Truncate in SQL Server

Delete vs Truncate
 
Delete

• Delete command removes the rows from a table based on the condition that we provide with a WHERE clause
• DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
• DELETE does not reset identity of the table. So, if you want to retain the identity counter, use DELETE.
• DELETE Activates Triggers.
• DELETE Can be Rolled back.
• DELETE is DML Command.

Truncate

• Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.
• TRUNCATE removes the data by de-allocating the data pages used to store the table’s data, and only the page de-allocations are recorded in the transaction log.
• TRUNCATE Resets identity of the table.
• Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
• TRUNCATE can not be Rolled back.
• Truncate is a DDL Command.
• You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.