SQL TRUNCATE TABLE Statement

Summary: in this tutorial, you will learn how to use the SQL TRUNCATE TABLE statement to remove all data in a table efficiently and fast.

Introduction to the SQL TRUNCATE TABLE statement #

To delete all data from a table, you use the DELETE statement without a WHERE clause:

DELETE FROM table_name;

However, the DELETE statement is slow and not efficient for deleting all rows from a table with a lot of data. The reason is that the database system may need to acquire a lock and write a log for each individual row.

To reduce locking and logging overhead, you can use the TRUNCATE TABLE statement. The TRUNCATE TABLE statement deletes all data from a table very fast.

Here’s the syntax of TRUNCATE TABLE statement:

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

In this syntax, you specify the table_name that you want to delete data after the TRUNCATE TABLE keyword.

Some database systems such as MySQL and PostgreSQL allow you to skip the TABLE keyword so the TRUNCATE TABLE statement is as simple as follows:

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

When you issue the TRUNCATE TABLE statement, the database system deletes all rows from the table by deallocating the data pages allocated by the table. By doing this, the database system can reduce the resources for logging and locking.

To truncate multiple tables at once, you can specify a list of comma-separated table names in the TRUNCATE TABLE clause:

TRUNCATE TABLE table_name1, table_name2, ...;Code language: SQL (Structured Query Language) (sql)

Not all database systems support this form of the TRUNCATE TABLE statement. If you are using the one that does not, you must issue multiple TRUNCATE TABLE statements to truncate multiple tables.

SQL TRUNCATE TABLE statement example #

First, create a new table called items :

CREATE TABLE items (
  id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
);Code language: SQL (Structured Query Language) (sql)

Try it

Second, execute the following INSERT statement as many times as you want to insert data into the items table:

INSERT INTO
  items (id)
VALUES
  (DEFAULT);Code language: SQL (Structured Query Language) (sql)

Try it

Note that if you use a database system that supports user-defined functions or stored procedures, you can put this statement inside a loop.

For example, the following procedure in PostgreSQL loads data into the items table with the number of rows specified by the n parameter.

CREATE PROCEDURE load_data (n INT) AS $$
BEGIN
  FOR i IN 1..n LOOP
    INSERT INTO items(id) VALUES(DEFAULT);
  END LOOP;
END;  
$$ LANGUAGE plpgsqlCode language: SQL (Structured Query Language) (sql)

Try it

The following statement calls the load_data procedure to insert 10,000 rows into the items table.

CALL load_data(10000);Code language: SQL (Structured Query Language) (sql)

Try it

Third, use the TRUNCATE TABLE statement to truncate data from the items table:

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

Try it

SQL TRUNCATE TABLE vs. DELETE Statement #

The following table compares the SQL DELETE and TRUNCATE TABLE statements:

Aspect	DELETE	TRUNCATE TABLE
Purpose	Deletes one or some rows from a table based on a `WHERE` clause. Without a `WHERE` clause, the `DELETE` statement also delete all rows from a table.	Deletes all rows from a table.
Conditional Removal	Allows to specify a condition for specifying rows to delete in the `WHERE` clause.	Does not support conditions.
Transaction Support	Logs the delete operations and and supports rollbacks as part of a transaction.	Provides very minimal logging. Some database system also supports rollbacks in transactions.
Performance	Slower	Faster
Triggers	Activates `DELETE` triggers.	Does not activate `DELETE` triggers. Some database systems support TRUNCATE triggers.
Identity Column Reset	Does not reset the value of identity columns (auto-increment).	Resets the value of identity columns to their seed value.
Foreign Key Constraints	Follows foreign key constraints.	Cannot truncate a table referenced by a foreign key. Some database systems support `CASCADE` option to delete foreign key constraints before truncating table
Usage	When you want to delete specific rows based on a condition or need to use DELETE triggers	When you need to quickly delete all from a table fast.

Summary #

Use the TRUNCATE TABLE statement to delete all rows from a table.

Databases #

Was this tutorial helpful ?