SQL TRUNCATE TABLE

Last Updated : 31 Oct, 2025

The TRUNCATE TABLE statement in SQL is used to quickly remove all rows from a table while keeping its structure intact. It is commonly preferred over the DELETE statement when dealing with large datasets because it operates much faster. In most databases, TRUNCATE cannot be rolled back once executed.

Example: First, we will create a demo SQL database and table, on which we will use the TRUNCATE TABLE command.

Query:

TRUNCATE TABLE students;

Output:

Syntax

TRUNCATE TABLE table_name;

Example of SQL TRUNCATE TABLE

Let's understand TRUNCATE in SQL with examples. Here we will look at different examples of the SQL TRUNCATE TABLE command.

First, we will create a demo SQL database and table, on which we will use the TRUNCATE TABLE command.

SQL

CREATE TABLE EMPLOYEE(
    EMP_ID INT(4),
    NAME VARCHAR(20),
    AGE INT(3),
    DOB DATE,
    SALARY DECIMAL(7,2)
);

INSERT INTO EMPLOYEE VALUES (121, 'AJAY KUMAR', 23, '1987-09-12', 23456.32);
INSERT INTO EMPLOYEE VALUES (132, 'BOBBY DEOL', 34, '1989-02-19', 84164.56);
INSERT INTO EMPLOYEE VALUES (246, 'ELVISH SMITH', 27, '1996-01-29', 51876.97);
INSERT INTO EMPLOYEE VALUES (955, 'GEORGE CLARKE', 31, '1992-09-21', 91451.64);
INSERT INTO EMPLOYEE VALUES (729, 'MONICA GELLER', 28, '1985-11-18', 98329.43);

The following table will be created.

Example of TRUNCATE TABLE in SQL

In this example, we will Truncate the created table.

Query:

TRUNCATE TABLE EMPLOYEE;

Output:

After truncating data of our table, the data of our table has been erased but the structure is preserved so now if we perform SELECT * FROM EMPLOYEE command on our table we will see everything is erased and an empty set is being returned.

But let's now check whether the structure of the table is deleted or it has been preserved so we again use the DESC command to see the structure of the table and we will see that the structure remains as it is.

SQL TRUNCATE vs DELETE

Here's a comparison of the TRUNCATE and DELETE statements in SQL presented in a tabular format:

TRUNCATE TABLE	DELETE
Removes all rows from a table	Removes rows based on a `WHERE` clause or all rows if no condition is specified
Not supported	Supported
Minimal logging (usually faster)	Fully logged (can be slower)
Generally cannot be rolled back in some DBMS	Can be rolled back if within a transaction
Does not fire triggers	Fires triggers
Cannot truncate a table referenced by a foreign key (without disabling the constraint)	Can delete rows in a table referenced by a foreign key
Resets identity seed value (auto-increment counter)	Does not reset the identity seed value
Generally faster for large data volumes	Can be slower, especially for large data volumes
Typically used to quickly empty a table	Used to remove specific rows based on a condition
Releases the storage space used by the table rows	Does not automatically reclaim space, may require a `VACUUM` or similar command
Retains the table structure, constraints, and indexes	Retains the table structure, constraints, and indexes

This table should help clarify the differences between TRUNCATE TABLE and DELETE

SQL TRUNCATE vs DROP

Here's a comparison of the SQL TRUNCATE TABLE and DROP TABLE commands in a tabular format:

TRUNCATE TABLE	DROP TABLE
Removes all rows from a table, leaving the structure intact.	Deletes the entire table, including its structure.
Generally faster than `DELETE` since it deallocates data pages.	Fast operation since it removes both data and structure.
Minimal logging; typically logs page deallocations only.	Fully logged; the entire table drop is recorded.
Retained; only the data is removed.	Deleted; table structure and data are both removed.
Resets the auto-increment counter to the seed value (if present).	No impact, as the entire table is removed.
Triggers are not fired.	Not applicable, as the table no longer exists.
Cannot truncate a table if it is referenced by a foreign key.	Cannot drop a table if other tables reference it unless the foreign key constraint is removed first.
Used when you need to remove all data from a table but keep the table itself.	Used when you want to completely remove the table from the database.
Data cannot be recovered unless a backup is available (depends on the database system).	The table and its data cannot be recovered unless a backup is available.
Requires `ALTER` permission on the table.	Requires `DROP` permission on the table.