Skip to content
C

TRUNCATE TABLE

TRUNCATE TABLE

Definition

TRUNCATE TABLE quickly removes all rows from a table while keeping its structure (columns, constraints, indexes) intact. It is optimized for speed: rather than deleting rows one at a time, it deallocates the data pages wholesale.

sql
TRUNCATE TABLE staging_orders;

-- Postgres extras:
TRUNCATE TABLE staging_orders RESTART IDENTITY CASCADE;

TRUNCATE vs DELETE (Recap and Contrast)

AspectTRUNCATE TABLEDELETE
Row selectionWhole table only, no WHERECan filter with WHERE
Speed on large tablesVery fast (deallocates pages)Slower (row-by-row, fully logged)
LoggingMinimalFull row-level logging
TriggersRow-level triggers typically do not fireRow-level triggers fire normally
Identity/auto-incrementResets the counter (engine-dependent, see below)Does not reset the counter
Transactional rollbackDialect-dependent — see belowAlways rollback-able within a transaction

The Dialect Nuance on Transactionality (Important)

A common but overly broad claim is "TRUNCATE cannot be rolled back." This is engine-dependent:

  • PostgreSQL and SQL Server: TRUNCATE genuinely can be run inside a transaction and rolled back before commit — it is fully transactional there.
  • MySQL/InnoDB: TRUNCATE TABLE causes an implicit commit (like other DDL), so it effectively cannot be rolled back once executed, behaving much more like DROP TABLE+CREATE TABLE under the hood.

Always verify this behavior on your specific engine before relying on it in a script that assumes rollback safety.

Worked Example

A nightly ETL job needs to clear a staging table before reloading fresh data, and wants the identity counter reset for cleanliness:

sql
BEGIN;
TRUNCATE TABLE staging_orders RESTART IDENTITY;
COPY staging_orders FROM '/data/today_orders.csv' WITH (FORMAT csv);
COMMIT;   -- on Postgres, this whole block is atomic; a failure before COMMIT rolls everything back

Using DELETE FROM staging_orders; instead would work too, but would be dramatically slower on a multi-million-row staging table and would generate far more transaction log/WAL volume.

Edge Cases and Pitfalls

  • Fails in the presence of foreign keys referencing the table (in Postgres/SQL Server) unless CASCADE is used or the referencing rows are handled — you can't truncate a table that other rows still point to.
  • Resets auto-increment/identity counters by default in many engines (e.g., MySQL always resets; Postgres requires the explicit RESTART IDENTITY option, otherwise the counter is left untouched by default CONTINUE IDENTITY).
  • Does not fire row-level `AFTER DELETE` triggers in most engines — code relying on delete triggers for auditing will silently miss truncations.
  • Despite being "DDL-like" in behavior, syntactically it still reads like a DML statement (TRUNCATE TABLE x;), which confuses newcomers about why it resets identity counters while DELETE FROM x; does not.

Key Takeaways / Q&A

Q: Can I TRUNCATE just some rows matching a condition? A: No — TRUNCATE always affects 100% of the table's rows; for a filtered removal use DELETE ... WHERE ....

Q: Does TRUNCATE reset auto-increment counters? A: Typically yes (this is one of its defining differences from DELETE), though the exact default/option varies by engine (Postgres needs RESTART IDENTITY explicitly).

Mock Test

  • TRUNCATE TABLE - Quick Test

    8 questions on TRUNCATE TABLE.

    8 questions · 8 min · Medium
    Start Mock Test