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 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 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#