SQL TRUNCATE TABLE Statement

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;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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
);Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the big_table:

INSERT INTO
  big_table (name)
VALUES
  ('MySQL'),
  ('PostgreSQL'),
  ('Oracle'),
  ('Microsoft SQL Server')Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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

TRUNCATE TABLE big_table;Code language: SQL (Structured Query Language) (sql)

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.

Overview of SQL TRUNCATE TABLE Statement

SQL TRUNCATE TABLE Statement Example

SQL TRUNCATE TABLE vs. DELETE

Summary

Databases