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 remove all rows in a big table fast and efficient, you use the following TRUNCATE TABLE statement:
TRUNCATE TABLE table_name;
You specify the
table_name that you want to delete data after the TRUNCATE TABLE clause. Some database systems such as MySQL, PostgreSQL, etc., allow you to skip the TABLE keyword so the TRUNCATE TABLE statement is simple as the following statement:
When you issue the TRUNCATE TABLE statement, the RDBMS removes all rows in 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 remove all data in a table. However, they have some differences listed as following:
- When you use the DELETE statement, the RDBMS 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 on a table referenced by a foreign key constraint, cannot use the TRUNCATE TABLE statement. In this case, you must use the DELETE statement instead.
- The TRUNCATE TABLE statement does not activate a trigger because it is not logged.
- Some database systems reset the count of an AUTO_INCREMENT (or identity, sequence, etc.) column to its start value.
- You can use the WHERE clause in the DELETE statement to remove partial data of a table, however, you cannot have the WHERE clause with the TRUNCATE TABLE statement.
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 big_table table:
INSERT INTO big_table (val)
Note that if you use an RDBMS 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 call the
load_big_table_data to load 10000 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 is.
Now you should know how to use TRUNCATE TABLE statement to remove all data in a big table fast and understand the differences between the TRUNCATE TABLE and DELETE statements.