# SQL TRUNCATE TABLE: Remove All Rows from a Table Quickly

- URL: https://betteranalyst.com/sql/truncate-table

SQL guide · Tables, views & databases (DDL)

The `TRUNCATE TABLE` statement deletes all rows from a table in one fast operation and keeps the table definition, its columns, indexes and constraints in place. After it runs you have an empty table that is ready to be filled again. It is typically used to reset staging tables before a data load, to clear out test data, or to empty a log table that has grown too large.

It is usually much faster than `DELETE FROM table_name` without a `WHERE` clause. Instead of removing rows one at a time and logging each deletion, most databases deallocate the data pages in bulk and log only that action. The trade-off is that you cannot filter rows, since truncate always removes everything, and it often resets auto-increment or identity counters back to their starting value.

Truncate behaves differently from delete in other ways too. It generally does not fire row-level delete triggers, it can require stronger privileges, and it may be blocked when other tables hold foreign keys that reference the table. Whether it can be rolled back depends on the database. Compare the options carefully before choosing: use `DELETE` to remove some rows, `TRUNCATE TABLE` to remove all rows but keep the table, and `DROP TABLE` to remove the table itself.

## Basic syntax

```
TRUNCATE TABLE table_name;

-- PostgreSQL extras
TRUNCATE TABLE table_name [, table_name2 ...]
  [RESTART IDENTITY | CONTINUE IDENTITY]
  [CASCADE | RESTRICT];
```

The table name is the only required part. In PostgreSQL the keyword `TABLE` is optional, you can list several tables, `RESTART IDENTITY` resets sequences, and `CASCADE` also truncates tables with foreign keys pointing at it.

## SQL TRUNCATE TABLE examples

### Empty a staging table

This removes every row from staging\_orders while keeping its columns and indexes. It is a common first step in a load job: clear the table, then insert the fresh batch.

```
TRUNCATE TABLE staging_orders;

INSERT INTO staging_orders (order_id, customer_id, total)
SELECT order_id, customer_id, total
FROM raw_orders;
```

### Truncate and reset the ID counter in PostgreSQL

With `RESTART IDENTITY` the sequence behind an identity or serial column goes back to 1, so new rows start numbering from the beginning. Without it, the counter would continue from where it left off.

```
TRUNCATE TABLE event_log RESTART IDENTITY;
```

### Truncate tables that have foreign keys

A table referenced by a foreign key cannot be truncated on its own in most systems. In PostgreSQL you can truncate the parent and child together, or add `CASCADE` to include every referencing table. Be sure you want those tables emptied too.

```
TRUNCATE TABLE order_items, orders CASCADE;
```

### Compare with DELETE

A `DELETE` without a filter also empties the table but works row by row, logs each row and fires triggers. Use it when you need those behaviors or when you want a filtered removal instead of clearing everything.

```
-- Remove only old rows
DELETE FROM event_log
WHERE created_at < '2026-01-01';

-- Remove every row, row by row
DELETE FROM event_log;
```

## Where SQL dialects differ

**PostgreSQL:** Transactional, so a truncate can be rolled back inside a transaction. Supports multiple tables, `RESTART IDENTITY` and `CASCADE`. It takes an `ACCESS EXCLUSIVE` lock on the table.

**MySQL:** Behaves like a drop and recreate of the table, causes an implicit commit and cannot be rolled back. It resets `AUTO_INCREMENT` to its initial value. It fails on InnoDB tables referenced by foreign keys unless checks are disabled.

**SQL Server:** Can be rolled back inside a transaction. It resets `IDENTITY` to the seed. It is not allowed on a table referenced by a foreign key, even if the child table is empty. It requires `ALTER TABLE` permission.

**SQLite:** There is no `TRUNCATE TABLE` statement. Use `DELETE FROM table_name`, which SQLite optimizes into a fast truncate when there is no `WHERE` clause, and reset autoincrement counters by clearing the `sqlite_sequence` entry.

**Oracle:** Is DDL and commits implicitly, so it cannot be rolled back. Supports `TRUNCATE TABLE t DROP STORAGE` or `REUSE STORAGE`, and `CASCADE` from 12c to include child tables with `ON DELETE CASCADE`.

## Common mistakes

- Expecting to filter rows with `WHERE`. Truncate has no filter and always removes everything, so use `DELETE` for partial removal.
- Assuming it can be rolled back. In MySQL and Oracle it commits immediately, so a mistake is permanent without a backup.
- Running it on a table that other tables reference and being surprised by a foreign key error. Truncate dependents first, or use `CASCADE` where available.
- Relying on delete triggers to fire for auditing. Truncate normally bypasses row-level triggers.
- Confusing it with `DROP TABLE`. Truncate keeps the table, while drop removes the definition too.

## Related SQL guides

- [SQL DELETE](https://betteranalyst.com/sql/delete)
- [SQL DROP TABLE](https://betteranalyst.com/sql/drop-table)
- [SQL ALTER TABLE](https://betteranalyst.com/sql/alter-table)
- [SQL CREATE TABLE](https://betteranalyst.com/sql/create-table)
- [SQL FOREIGN KEY](https://betteranalyst.com/sql/foreign-key)
- [SQL INSERT INTO SELECT](https://betteranalyst.com/sql/insert-into-select)

[Browse all SQL guides](https://betteranalyst.com/sql)

## Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

[Try the AI SQL generator](https://betteranalyst.com/ai-sql-query-generator)