SQL TRUNCATE TABLE

Summary: in this tutorial, you’ll learn how to use SQL TRUNCATE TABLE statement to remove all rows from a table quickly and efficiently.

Overview of SQL TRUNCATE TABLE Statement #

The DELETE statement with a WHERE clause allows you to delete all rows from a table:

DELETE FROM table_name;Copy

However, it is inefficient to use the DELETE statement to remove all rows from a large table.

To delete all rows from a large table quickly, you can use the TRUNCATE TABLE statement.

Here’s the syntax of the TRUNCATE TABLE statement:

TRUNCATE TABLE table_name;Copy

In this syntax, you specify the name of the table that you want to remove all rows after the TRUNCATE TABLE keywords.

SQL TRUNCATE TABLE Statement Example #

Let’s practice the TRUNCATE TABLE statement to get a better understanding.

First, create a table named big_table with two columns id and name:

CREATE  TABLE big_table(
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(45) NULL
);Copy

Second, insert some rows into the big_table:

INSERT INTO
  big_table (name)
VALUES
  ('MySQL'),
  ('PostgreSQL'),
  ('Oracle'),
  ('Microsoft SQL Server')Copy

In practice, the big_table will have a million rows to see the difference between the DELETE statement and the TRUNCATE TABLE statement.

Third, query the data from the big_table table:

SELECT * FROM big_table;Copy

Finally, use the TRUNCATETABLE statement to remove all rows in the big_table table.

TRUNCATE TABLE big_table;Copy

If you query the big_table table again, you won’t see any data because the big_table table is empty

SQL TRUNCATE TABLE vs. DELETE #

Feature	TRUNCATE TABLE	DELETE
Syntax	TRUNCATE TABLE table_name;	DELETE FROM table_name;
Operation	Removes all rows from a table quickly and efficiently	Deletes all rows from a table.
Transaction	Not logged, cannot be rolled back	Logged, can be rolled back within a transaction
Locking behavior	Obtains a table-level lock (depending on the RDBMS)	Can lock rows being deleted to prevent changes
Reset Identity	Resets identity columns to their seed value (in some RDBMS)	Doesn’t reset identity columns
Performance	Generally faster than DELETE	Slower than TRUNCATE in most cases
Triggers	Doesn’t activate DELETE triggers. Some RDBMS support TRUNCATE triggers.	Activates DELETE triggers

Summary #

• Use the SQL TRUNCATE TABLE statement to remove all rows in a table quickly and efficiently.

Databases #

• PostgreSQL TRUNCATE TABLE Statement
• MySQL TRUNCATE TABLE Statement
• MariaDB TRUNCATE TABLE Statement
• Oracle TRUNCATE TABLE Statement
• SQL Server TRUNCATE TABLE Statement
• Db2 TRUNCATE TABLE Statement

Was this helpful?

YesNo