Difference between DELETE, TRUNCATE, and DROP
Difference between DELETE, TRUNCATE, and DROP
SQL provides three distinct operations for removing data, differing in speed, scope, rollback capabilities, and trigger activation.
Comparison Table
| Feature | DELETE | TRUNCATE | DROP |
|---|---|---|---|
| Command Type | DML | DDL | DDL |
| Target Scope | Specific rows (or all rows) | All rows | Entire table + data + structure |
| WHERE Clause | Supported (WHERE) | Not supported | Not supported |
| Execution Speed | Slower (logged per row) | Extremely fast (deallocates pages) | Extremely fast |
| Row Triggers | Fires DELETE triggers | Does NOT fire triggers | Does NOT fire triggers |
| Reset Identity | No | Yes (resets counter to seed) | Table destroyed |
| Table Structure | Preserved | Preserved | Completely removed |
Key Interview Takeaways
- DELETE: Use when removing filtered rows or when audit triggers must fire.
- TRUNCATE: Use when clearing an entire staging or cache table quickly. It releases disk pages directly rather than writing each row deletion to the log.
- DROP: Use when decommissioning a table entirely.