|
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.
|
Delete command removes the
rows from a table based on the condition that we provide with a WHERE clause
|
|
TRUNCATE is faster and uses
fewer system and transaction log resources than DELETE.
|
DELETE removes rows one at a
time and records an entry in the transaction log for each deleted row.
|
|
TRUNCATE Resets identity of
the table
|
DELETE does not reset
identity of the table.
|
|
TRUNCATE removes all 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.
|
DELETE Can be used with or
without a WHERE clause
|
|
You cannot use TRUNCATE TABLE
on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is
not logged, it cannot activate a trigger.
|
DELETE Activates Triggers.
|
|
TRUNCATE cannot be rolled
back.
|
DELETE can be rolled back.
|
|
TRUNCATE is DDL Command.
|
DELETE is DML Command.
|
|
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.
|
If you want to retain the
identity counter, use DELETE instead. If you want to remove table definition
and its data, use the DROP TABLE statement. |