List down the difference between DELETE and TRUNCATE command?

1.	List down the difference between DELETE and TRUNCATE command?
Answer» Let me list down some basic difference then we will try to understand with examples as well : Truncate is lightning fast when we compare it with DELETE commands. The reason for the same is less usage of transaction logs in Truncate. DELETE erase records one by one and at the same time transaction logs get maintained for the get for every record. TRUNCATE erases the records by deallocating space from PAGES and it makes a single entry in logs. Triggers are not fired in case of TRUNCATE while delete command fires trigger. The identity column values get reset in case of TRUNCATE while delete does not reset the identity column value. In the case of foreign KEY constraint or tables used in REPLICATION, Truncation happen. DELETE falls into the DML category while TRUNCATION falls into the DDL category of commands. Please consider below example where EVEN after deleting records when transaction was rolled back, it reverted the changes :

Answer»

Let me list down some basic difference then we will try to understand with examples as well :

Truncate is lightning fast when we compare it with DELETE commands. The reason for the same is less usage of transaction logs in Truncate. DELETE erase records one by one and at the same time transaction logs get maintained for the get for every record. TRUNCATE erases the records by deallocating space from PAGES and it makes a single entry in logs.
Triggers are not fired in case of TRUNCATE while delete command fires trigger.
The identity column values get reset in case of TRUNCATE while delete does not reset the identity column value.
In the case of foreign KEY constraint or tables used in REPLICATION, Truncation happen.
DELETE falls into the DML category while TRUNCATION falls into the DDL category of commands.

Please consider below example where EVEN after deleting records when transaction was rolled back, it reverted the changes :

Discussion