Generally Delete command removes the rows from a table based on some where condition. whereas Truncate removes all the rows from a table.
Truncate
Truncate
- Truncate is faster and uses fewer system and transaction log resources than Delete.
- You cannot use Truncate Table on a table referenced by a FOREIGN KEY constraint.
- Truncate is a DDL Command.
- Truncate resets the identity of the table.
- 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.
- 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 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.
- Delete removes rows one at a time and records an entry in the transaction log for each deleted row.
- Delete does not reset the identity of the table.
- Delete is DML Command.
- Delete can be rolled back.
- 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 table.
