What is the Difference between DELETE and TRUNCATE Commands?

The delete command removes the rows from a table on the basis of the condition that we provide with a WHERE clause. 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

• TRUNCATE is faster and uses fewer system and transaction log resources than DELETE. (Read all the points below)
• 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 the 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.
• You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
• Using T-SQL – TRUNCATE cannot be rolled back unless it is used in TRANSACTION. OR TRUNCATE can be rolled back when used with BEGIN … END TRANSACTION using T-SQL.
• TRUNCATE is a DDL Command.
• TRUNCATE resets the identity of the table.

DELETE

• DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
• DELETE does not reset Identity property of the table.
• DELETE can be used with or without a WHERE clause
• DELETE activates Triggers if defined on the table.
• DELETE can be rolled back.
• DELETE is DML Command.
• DELETE does not reset the identity of the table.