What is the difference between DELETE & TRUNCATE commands in SQL?

DELETE command removes the rows from a table based on the condition that we provide with a WHERE clause. TRUNCATE command actually removes all the rows from a table and there will be no data in the table after we run the TRUNCATE command.

TRUNCATE

• TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
• TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.
• TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain.
• TRUNCATE always locks the table and the data page but not each and every row.
• TRUNCATE cannot be used on a table referenced by a FOREIGN KEY constraint.
• TRUNCATE cannot be used with a WHERE clause.
• TRUNCATE cannot activate a trigger because the operation does not log individual row deletions.
• TRUNCATE can not be Rolled back using logs.
• TRUNCATE is DDL Command.
• TRUNCATE Resets identity of the table. The counter used by an identity for new rows is reset to the seed for the column.

SYNTAX: TRUNCATE TABLE #table_name#

DELETE

• DELETE is slower than TRUNCATE because it maintains log for every record.
• DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
• DELETE locks each row in a table for deletion.
• DELETE can be used with or without a WHERE clause.
• DELETE activates Triggers.
• DELETE can be Rolled back using logs.
• DELETE is DML Command.
• DELETE does not reset identity of the table.

SYNTAX: DELETE FROM #table_name# WHERE #condition#