SQL TRUNCATE TABLE

What does SQL TRUNCATE TABLE do?

TRUNCATE TABLE instantly removes all rows from a table and typically reclaims the storage in a minimally logged, DDL-style operation.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL TRUNCATE TABLE: Supported: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, Snowflake, Redshift. Not supported: SQLite (use DELETE).

SQL TRUNCATE TABLE Full Explanation

TRUNCATE TABLE is a Data Definition Language (DDL) command that deletes every row in a table without scanning each row individually. Because most engines log only page de-allocations rather than row-level deletes, the operation is much faster and uses far less transaction log space than DELETE. In many systems it resets AUTO_INCREMENT or IDENTITY values and releases the physical storage back to the tablespace. Truncation cannot include a WHERE clause, cannot be used on views, and is blocked when foreign-key references exist unless the dialect supports CASCADE. In PostgreSQL and Oracle, TRUNCATE can run inside a transaction and can be rolled back; in MySQL the statement issues an implicit COMMIT before and after execution. Triggers defined for DELETE events do not fire, but dialect-specific TRUNCATE triggers may. Permissions usually require the ALTER or DROP privilege on the target table.

SQL TRUNCATE TABLE Syntax

TRUNCATE TABLE schema_name.table_name
[ RESTART IDENTITY | CONTINUE IDENTITY ]
[ CASCADE | RESTRICT ];

SQL TRUNCATE TABLE Parameters

table_name (identifier) - Name of the table to truncate. Required.
RESTART IDENTITY (keyword) - Reset sequences associated with the table to their start values (PostgreSQL).
CONTINUE IDENTITY (keyword) - Keep current sequence values (PostgreSQL default).
CASCADE (keyword) - Automatically truncate tables that have foreign-key references to the target table (PostgreSQL, Oracle).
RESTRICT (keyword) - Refuse to truncate if foreign-key references exist (default).

Example Queries Using SQL TRUNCATE TABLE

-- 1. Quickly empty a staging table
TRUNCATE TABLE staging_events;

-- 2. Reset identity values and cascade to dependent tables (PostgreSQL)
TRUNCATE TABLE public.users RESTART IDENTITY CASCADE;

-- 3. Test in a transaction and roll back
BEGIN;
TRUNCATE TABLE temp_import;
ROLLBACK;

Expected Output Using SQL TRUNCATE TABLE

All rows in the specified table are removed instantly
The database returns a command-tag such as "TRUNCATE TABLE" or the number of truncated tables
No result set is returned
Storage is reclaimed and identity columns may reset depending on options

Use Cases with SQL TRUNCATE TABLE

Refreshing staging or ETL landing tables between loads
Quickly clearing temporary or audit tables during tests
Resetting identity counters before repopulating a table
Freeing disk space after archiving data elsewhere

Common Mistakes with SQL TRUNCATE TABLE

Attempting to use a WHERE clause (use DELETE instead)
Expecting DELETE triggers to fire
Truncating a table referenced by a foreign key without CASCADE support
Assuming the operation can be rolled back in MySQL or older SQL Server versions
Forgetting that TRUNCATE may reset AUTO_INCREMENT or IDENTITY values

First Introduced In

SQL:2008

Frequently Asked Questions

Is TRUNCATE TABLE faster than DELETE?

Yes. Because the database de-allocates entire data pages instead of logging each row delete, TRUNCATE is significantly faster and uses far less transaction log space.

Does TRUNCATE TABLE reset identity or auto_increment values?

It depends on the dialect. MySQL and SQL Server reset the counter automatically. PostgreSQL requires the RESTART IDENTITY option, while Oracle leaves identity values unchanged.

Can a TRUNCATE TABLE be rolled back?

PostgreSQL and Oracle allow rollback if executed inside an explicit transaction. MySQL and older SQL Server versions wrap the command in an implicit COMMIT, so the operation cannot be undone.

Will DELETE triggers fire on TRUNCATE TABLE?

No. TRUNCATE is treated as a DDL command, so standard DELETE triggers do not execute. Some databases offer dedicated TRUNCATE triggers that must be created separately.