TRUNCATE TABLE removes all rows from a table instantly, but keeps the table structure intact — ready to insert into again.
Syntax
TRUNCATE TABLE table_name;Example
TRUNCATE TABLE staging_orders;Every row in staging_orders is gone; the table, its columns, indexes, and constraints remain.
TRUNCATE vs DELETE — The Classic Interview Comparison
| TRUNCATE | DELETE | |
|---|---|---|
| Removes | All rows only | Rows matching WHERE (or all, if no WHERE) |
| WHERE clause? | Not allowed | Allowed |
| Speed on large tables | Much faster — deallocates data pages | Slower — removes row by row, logged individually |
| Auto-increment counter | Resets to start (in most databases) | Keeps counting from where it left off |
| Triggers | Usually does NOT fire DELETE triggers | Fires DELETE triggers |
| Transactional? | PostgreSQL/SQL Server: yes. MySQL: implicit commit, harder to roll back | Fully transactional (COMMIT/ROLLBACK) |
Practical Use Case
Clearing a staging table between nightly ETL loads, or resetting a test/demo table — anywhere you want "empty, like new" rather than selectively removing rows.
Common Mistakes
- Trying to add a
WHEREclause toTRUNCATE— it doesn't support one; useDELETEif you need conditional removal - Assuming
TRUNCATEis always safely reversible withROLLBACK— in MySQL, it behaves like DDL and commits immediately - Truncating a table that a foreign key still references — most databases will block this unless the constraint allows it
Interview Relevance
"Explain the difference between DELETE, TRUNCATE, and DROP" is one of the most frequently asked SQL basics questions. Structure your answer around: what's removed, whether WHERE is allowed, and transactional behavior — exactly the table above.
Practice Question
You need to remove only orders placed before 2023 from a table, not all of them. Should you use TRUNCATE or DELETE? Why?
