MS SQL Delete vs Truncate

Both DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION (if the current session is not closed). Other differences are as follows,

Truncate:

• Truncate is faster and uses fewer system and transaction log resources than Delete.
• 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 removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column.
• Truncate cannot be TABLE on a table referenced by a FOREIGN KEY constraint. Because Truncate TABLE is not logged, it cannot activate a trigger.
• Truncate cannot be rolled back.
• Truncate is DDL Command.
• Truncate Resets identity of the table

Delete:

• Delete removes rows one at a time and records an entry in the transaction log for each Deleted row.
• If you want to retain the identity counter, use Delete instead. If you want to remove table definition and its data, use the DROP TABLE statement.
• Delete Can be used with or without a WHERE clause
• Delete Activates Triggers.
• Delete can be rolled back.
• Delete is DML Command.
• Delete does not reset identity of the table.