1. TRUNCATE is a DDL (Data Definition Language) command and DELETE is a DML (Data Manipulation Language) command.
2. You can use WHERE clause with DELETE but
not with TRUNCATE .
3. You cannot rollback data in TRUNCATE but in
DELETE it is possible to rollback data.
4. A trigger doesn't get fired in case of
TRUNCATE whereas Triggers get fired in case of a DELETE command.
5. TRUNCATE is faster than DELETE. TRUNCATE is
faster than DELETE due to the way TRUNCATE "removes" rows. Actually,
TRUNCATE does not remove data, but rather deallocates whole data pages and
removes pointers to indexes. The data still exists until it is overwritten or
the database is shrunk. This action does not require a lot of resources and is therefore
very fast.
6. TRUNCATE resets the Identity counter if
there is any identity column present in the table where DELETE does not reset
the identity counter.
7. You cannot TRUNCATE a table that has any
foreign key constraints. You will have to remove the contraints, TRUNCATE the
table, and reapply the contraints.
8. DELETE and TRUNCATE operations are both
logged. DELETE is a logged operation on a per row basis and TRUNCATE command
logs the deallocation of the data pages in which the data exists.
No comments:
Post a Comment