1.The DELETE command is used to remove rows from a table based on a WHERE
condition whereas TRUNCATE removes all rows from a table.
2. So we can use a where clause with DELETE to filter and delete specific records
whereas we cannot use a Where clause with TRUNCATE.
3. DELETE is executed using a row lock, each row in the table is locked for deletion
whereas TRUNCATE is executed using a table lock and the entire table is locked for
removal of all records.
4. DELETE is a DML command whereas TRUNCATE is a DDL command.
5. DELETE retains the identity of the column value whereas in TRUNCATE, the Identify
column is reset to its seed value if the table contains any identity column.
6. To use Delete you need DELETE permission on the table whereas to use Truncate on
a table you need at least ALTER permission on the table.
7. DELETE uses more transaction space than the TRUNCATE statement whereas
Truncate uses less transaction space than DELETE statement.
8. DELETE can be used with indexed views whereas TRUNCATE cannot be used with
indexed views.
9. The DELETE statement removes rows one at a time and records an entry in the
transaction log for each deleted row whereas TRUNCATE TABLE removes the data
by deallocating the data pages used to store the table data and records only the page
deallocations in the transaction log.
10. Delete activates a trigger because the operation is logged individually whereas
TRUNCATE TABLE can’t activate a trigger because the operation does not log
individual row deletions