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. 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.
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
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
DELETEstatement, 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 TABLEstatement, 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 TABLEstatement. In this case, you must use the
TRUNCATE TABLEstatement 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 TABLEstatement. It is not the case for the
DELETEstatement with a
WHEREclause deletes partial data from a table while the
TRUNCATE TABLEstatement 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,
Second, execute the following statement as many times as you want to insert sample data into the
INSERT INTO big_table (val)
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
CREATE PROCEDURE load_big_table_data(IN num int)
DECLARE counter int default 0;
WHILE counter < num DO
INSERT INTO big_table(val)
The following statement calls the
load_big_table_data stored procedure to insert 10,000 rows into the
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.
Now you should know how to use the
TRUNCATE TABLE statement to delete all data from a big table fast and understand the differences between the
TRUNCATE TABLE and