1. When we use DELETE, SQL
Server will maintain the transaction log. So that, it can restore the state (rows in
a table) when user requested rollback.
Where as in case of
truncate, SQL Server TRUNCATE logs the de-allocation of the data pages in which
the data exists. The de-allocation of data pages means that your
data rows still actually exist in the data pages, but the
extents have been marked as empty for reuse. This is what makes
TRUNCATE a faster operation to perform over DELETE.
2. In DELETE command we can
use WHERE clause, where as in TRUNCATE command, we cannot use any clauses.
3. You cannot TRUNCATE a
table that has any foreign key constraints. You will have to remove the constraints,
TRUNCATE the table, and re-apply the constraints.
4. TRUNCATE will reset any
identity columns to the default seed value.
5. DELETE causes triggers to fire where as TRUNCATE will not
