Difference between TRUNCATE & DELETE statement in SQL
Server
Truncate and Delete both is used to delete data
from the table. Both these commands will only delete the data of the specified
table; they cannot remove the whole table: data along with structure.
1)
TRUNCATE is a DDL (data definition language)
command whereas DELETE is a DML (data manipulation language) command.
2)
You can use WHERE clause (conditions) with
DELETE but you can't use WHERE clause with TRUNCATE.
3)
You can’t rollback data in TRUNCATE but in
DELETE you can rollback data. TRUNCATE removes (delete) the record permanently.
4)
A trigger doesn’t get fired in case of TRUNCATE
whereas Triggers get fired in DELETE command.
5)
If tables which are referenced by one or more
FOREIGN KEY constraints then TRUNCATE will not work.
6)
TRUNCATE resets the Identity counter if there is
any identity column present in the table where delete not resets the identity
counter.
7)
Delete and Truncate both are logged operation.
But DELETE is a logged operation on a per row basis and TRUNCATE logs the
deallocation of the data pages in which the data exists.
8)
TRUNCATE is faster than DELETE.