When to TRUNCATE and when to DELETE ??

Difference between Truncate and Delete in SQL

TRUNCATE is a DDL (data definition language) command whereas DELETE is a DML (data manipulation language) command.

You can use WHERE clause(conditions) with DELETE but you can't use WHERE clause with TRUNCATE .

You cann't rollback data in TRUNCATE but in DELETE you can rollback data.TRUNCATE removes(delete) the record permanently.

A trigger doesn’t get fired in case of TRUNCATE whereas Triggers get fired in DELETE command.

TRUNCATE resets the Identity counter if there is any identity column present in the table where delete not resets the identity counter.

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.

TRUNCATE is faster than DELETE.

Post Reference: Vikram Aristocratic Elfin Share