What is the difference between DELETE, TRUNCATE and DROP?
Quick Answer
DELETE is DML: removes rows (optionally with a WHERE), logs each row, fires triggers, keeps the table and can be rolled back. TRUNCATE is DDL: removes all rows by deallocating pages, minimally logged, very fast, resets IDENTITY, keeps the table structure, no row triggers. DROP is DDL: removes the entire table — data, structure, indexes, constraints, permissions.
DELETE is a DML statement — it removes rows one at a time, you can use a WHERE clause, it's fully logged, it fires triggers, and you can roll it back. TRUNCATE is DDL — it wipes the whole table by deallocating its pages, so it's very fast and minimally logged, it resets the identity seed, and it doesn't fire row triggers. DROP removes the table itself, including its structure, indexes and permissions. One correction people expect: TRUNCATE can be rolled back if it's inside an explicit transaction — it's minimally logged, not unlogged.
| DELETE | TRUNCATE | DROP | |
|---|---|---|---|
| Type | DML | DDL | DDL |
| Removes | Rows (WHERE allowed) | All rows | The whole table object |
| Logging | Fully logged per row | Minimally logged (page deallocation) | Minimal |
| Speed on big tables | Slow | Fast | Fast |
| IDENTITY seed | Unchanged | Reset | Gone with the table |
| Triggers | Fires DELETE triggers | No row triggers | No |
| Transaction / rollback | Yes | Yes (within an explicit transaction) | Yes (within an explicit transaction) |
| Restrictions | Few | Blocked if referenced by an FK, indexed view, etc. | Blocked if referenced by an FK |
Common myth to correct: TRUNCATE can be rolled back inside an explicit transaction in SQL Server — it is not “unrecoverable”, it is just minimally logged.