SQL TRUNCATE TABLE

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. For a big table that has few million rows, the DELETE statement is slow and not efficient.

To delete all rows from a big table fast, you use the following TRUNCATE TABLE statement:

TRUNCATE TABLE table_name;

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

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;

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 RDBMS can reduce the resources for logging and the number of locks that need to acquire.

To truncate multiple tables at a time, you can use a list of comma-separated table names after the TRUNCATE TABLE clause as follows:

TRUNCATE TABLE table_name1, table_name2, ...;

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 vs. DELETE

Logically the TRUNCATE TABLE statement and the DELETE statement without the WHERE clause gives the same effect that removes all data from a table. However, they do have some differences:

• When you use the DELETE statement, the database system logs the operations. And with some efforts, you can roll back the data that was deleted. However, when you use the TRUNCATE TABLE statement, you have no chance to roll back except you use it in a transaction that has not been committed.
• To delete data from a table referenced by a foreign key constraint, you cannot use the TRUNCATE TABLE statement. In this case, you must use the DELETE statement instead.
• The TRUNCATE TABLE statement does not fire the delete trigger if the table has the triggers associated with it.
• Some database systems reset the value of an auto-increment column (or identity, sequence, etc.) to its starting value after you execute the TRUNCATE TABLE statement. It is not the case for the DELETE statement.
• The DELETE statement with a WHERE clause deletes partial data from a table while the TRUNCATE TABLE statement always removes all data from the table.

SQL TRUNCATE TABLE examples

Let’s take a look at an example of truncating a table.

First, create a new table named big_table as follows:

CREATE TABLE big_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    val INT
);

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

INSERT INTO big_table (val)
VALUES
    (RAND(100000));

Note that if you use a database system that supports stored procedure, you can put this statement inside a loop. For example, the following stored procedure in MySQL loads data into the big_table table with the number of rows specified by the num parameter.

DELIMITER $$
CREATE PROCEDURE load_big_table_data(IN num int)
BEGIN
 DECLARE counter int default 0;
 
 WHILE counter < num DO
   INSERT INTO big_table(val)
   VALUES(RAND(1000000));
 END WHILE;
 
END$$

The following statement calls the load_big_table_data stored procedure to insert 10,000 rows into the big_table table.

CALL load_big_table_data(10000);

Third, to remove all data from the big_table, you use the following statement:

TRUNCATE TABLE big_table;

As you can see, how fast the TRUNCATE TABLE statement is.