SQL TRUNCATE

The TRUNCATE statement is used to remove all rows from a table at once — clearing the entire table — while keeping the table structure (columns, constraints, indexes) intact.

Think of TRUNCATE like emptying a glass completely and cleaning it out — the glass itself remains, ready to be refilled. The table stays, but all the data inside it is gone.

Syntax

TRUNCATE TABLE table_name;

Example

Before TRUNCATE

StudentID	StudentName	City
1	Ravi Sharma	Delhi
2	Priya Mehta	Mumbai
3	Arjun Nair	Chennai

TRUNCATE TABLE Students;

After TRUNCATE

StudentID	StudentName	City
(no rows)

The table is empty, but it still exists with all its columns and structure intact.

What Happens to AUTO_INCREMENT After TRUNCATE?

One important behaviour of TRUNCATE is that it resets the AUTO_INCREMENT counter back to 1. This means the next inserted row will get StudentID = 1 again.

-- After TRUNCATE, insert a new student
INSERT INTO Students (StudentName, City) VALUES ('Meera Joshi', 'Hyderabad');
SELECT * FROM Students;

Result:

StudentID	StudentName	City
1	Meera Joshi	Hyderabad

The AUTO_INCREMENT started from 1 again after the truncation.

TRUNCATE vs DELETE vs DROP — Comparison

Feature	TRUNCATE	DELETE (without WHERE)	DROP TABLE
What is removed?	All rows only	All rows only	Entire table (structure + data)
Table structure kept?	Yes	Yes	No
Can use WHERE clause?	No	Yes	No
Resets AUTO_INCREMENT?	Yes	No	N/A (table is gone)
Speed	Very fast	Slower (logs each row deletion)	Instant
Can be rolled back?	No (in most databases)	Yes (if inside a transaction)	No
SQL Command Type	DDL	DML	DDL
Fires row-level triggers?	No	Yes	No

Why TRUNCATE Is Faster Than DELETE

When using DELETE FROM table (without a WHERE clause), the database logs each individual row deletion in the transaction log — one entry per row. This allows the operation to be rolled back if needed.

TRUNCATE bypasses this row-by-row logging and deallocates the data pages directly. It operates at the storage level rather than the row level. This makes it significantly faster, especially on tables with millions of rows.

Limitations of TRUNCATE

TRUNCATE cannot be used on a table that is referenced by a FOREIGN KEY constraint from another table — this would violate referential integrity.
It does not activate row-level triggers (DELETE triggers will not fire).
In most databases, TRUNCATE is not easily reversible — once executed, the data is gone.
There is no WHERE clause — it removes all rows, no exceptions.

When to Use TRUNCATE

Use TRUNCATE When...	Use DELETE When...
All rows need to be removed quickly	Only specific rows need to be removed (using WHERE)
Resetting a staging or temporary table	Data removal needs to fire triggers
The AUTO_INCREMENT counter should reset to 1	The operation may need to be rolled back
Speed is critical (large tables)	Only a subset of rows should be deleted

Practical Example: Clearing a Temporary Table

-- A staging table used for importing data
CREATE TABLE TempImports (
    ImportID    INT AUTO_INCREMENT PRIMARY KEY,
    StudentName VARCHAR(50),
    City        VARCHAR(30)
);

-- After processing the imported data, clear the table for next use
TRUNCATE TABLE TempImports;

-- The table is now empty and ready for the next import batch
-- AUTO_INCREMENT is also reset to 1

Key Points to Remember

TRUNCATE removes all rows from a table but preserves the table structure.
It resets the AUTO_INCREMENT counter back to 1.
It is a DDL command (like CREATE and DROP), not a DML command like DELETE.
It cannot be used if the table is referenced by a FOREIGN KEY in another table.
It is much faster than DELETE for clearing large tables.
It cannot be rolled back easily — use with care.

Summary

TRUNCATE is the fastest way to clear all data from a table while keeping its structure. Unlike DELETE, it cannot target specific rows and cannot be easily undone. It also resets the AUTO_INCREMENT counter. It is best suited for quickly wiping temporary tables, staging areas, or test data between test runs.

Previous lessons

Back to courses

Next lessons