Part 39: Renaming & Truncating Tables: TRUNCATE vs DELETE
When you need to empty a table, should you use TRUNCATE TABLE or DELETE FROM?
1. Architectural Differences
| Feature | TRUNCATE TABLE |
DELETE FROM table |
|---|---|---|
| SQL Category | DDL | DML |
| Mechanics | Deallocates data pages instantly | Deletes rows one by one |
| Speed | Near-instantaneous (even on 100M rows) | Slow (writes each row deletion to undo/redo log) |
| WHERE Clause | ❌ No (empties entire table) | Yes |
| Triggers | Does NOT fire row-level triggers | Fires ON DELETE triggers |
| Identity Reset | Resets auto-increment counters to 1 | Does NOT reset counters |
Key Takeaways
- Use
TRUNCATEto wipe large staging/test tables instantly. - Use
DELETEwhen you need conditional row filtering or trigger execution.
In Part 40, we explore Dropping Tables: DROP TABLE with CASCADE vs RESTRICT!