TRUNCATE TABLE
SQL · Working with Tables
Theory
TRUNCATE TABLE removes every row from a table in one go while keeping the table itself: columns, data types, indexes, constraints and permissions all stay. TRUNCATE TABLE sales_temp leaves an empty sales_temp ready for the next load.
It is much faster than DELETE FROM sales_temp on large tables because it deallocates whole data pages instead of removing and logging each row, and it does not fire row-level DELETE triggers. It takes no WHERE clause; it is all or nothing. It is usually blocked when other tables reference this one with foreign keys. In SQL Server it resets identity columns to their seed; PostgreSQL resets them only with RESTART IDENTITY. Whether it can be rolled back depends on the engine: PostgreSQL and SQL Server allow it inside a transaction, while Oracle and MySQL commit it immediately. SQLite has no TRUNCATE; DELETE without WHERE is optimised instead.
Why we use it
Staging and temporary tables are reloaded constantly. TRUNCATE empties them almost instantly, without the time, log space and trigger side effects of deleting millions of rows one by one.
When to use it
Use TRUNCATE to reset staging tables, clear test data or wipe logs completely. Use DELETE when you need a WHERE clause, triggers to fire or guaranteed rollback, and DROP TABLE when the table itself should go.
How it works
- TRUNCATE TABLE sales_temp
- Referenced by foreign keys? Yes: Blocked (PostgreSQL offers CASCADE). No: Continue.
- Deallocate all data pages at once
- Keep columns, indexes, constraints and permissions
- SQL Server, or RESTART IDENTITY? Yes: Reset the identity counter. No: Counter keeps its value.
- Empty table, ready for the next load
Syntax
TRUNCATE TABLE table_name;Example
TRUNCATE TABLE sales_temp;
-- Clears every row from sales_temp instantly,
-- ready for tomorrow's data load — the schema is untouched.Key points
- Keeps the structure; removes every row
- No WHERE clause: all or nothing
- Much faster than DELETE on large tables and fires no DELETE triggers
- Usually blocked if foreign keys reference the table
- Rollback support varies: yes in PostgreSQL/SQL Server, no in Oracle/MySQL
- Best for resetting staging tables before each load