TRUNCATE TABLE
A developer has loaded 2 million test orders into a staging database and wants a clean slate before the next test run. DELETE FROM orders; works, but crawls through every row one at a time. TRUNCATE TABLE orders; finishes almost instantly. The difference is in what each one actually does.
The rule
TRUNCATE empties every row but keeps the table itself.
TRUNCATE TABLE orders;After this, orders still exists with all its columns, types and constraints — it just has zero rows. Compare it with its two neighbours:
| Removes rows | Can use WHERE | Keeps table structure | |
|---|---|---|---|
DELETE FROM orders WHERE … |
the ones you pick | yes | yes |
TRUNCATE TABLE orders |
all of them | no | yes |
DROP TABLE orders |
all of them | no | no — the table is gone |
TRUNCATE is fast because the database releases the table's storage in bulk rather than removing rows one by one. That speed has side effects:
- No WHERE. It is all rows or nothing.
- Row-level delete triggers do not fire, because rows are not deleted individually.
- Auto-numbering may reset. In MySQL, TRUNCATE restarts the auto-increment counter. In PostgreSQL it does so only if you write
TRUNCATE TABLE orders RESTART IDENTITY;. - Undo depends on the database. PostgreSQL can roll back a TRUNCATE inside a transaction. MySQL commits it immediately, so there is no undo.
- Referenced tables are protected. If
ordershas a foreign key pointing atcustomers, you cannot simply truncatecustomers; PostgreSQL requiresTRUNCATE … CASCADE, which also emptiesorders.
The shortcut
Picture a ladder of three rungs, from gentlest to most drastic: DELETE some → TRUNCATE all → DROP the table. Climb only as high as you need.
Do you need to keep any rows?
Yes → DELETE with a WHERE. Stop here.
Drills
Answer fast — trust the rule.
After TRUNCATE TABLE orders;, what does SELECT COUNT(*) FROM orders; return?
You need to remove only cancelled orders. Which statement fits?
Why is TRUNCATE usually much faster than DELETE FROM orders; on a huge table?
In MySQL, you run TRUNCATE by mistake and then run ROLLBACK. What happens?
Remember
- TRUNCATE empties every row but keeps the table itself.
- The ladder: DELETE some → TRUNCATE all → DROP the table.
- No WHERE, no per-row triggers, and in some databases no undo.