SQL TRUNCATE TABLE

Summary: in this tutorial, you are going to learn how to use SQL TRUNCATE TABLE statement to remove all rows in a table efficiently.

To delete all rows in a table, you can use SQL DELETE statement. However, with a table that has a large number of rows, the DELETE statement is not efficient. Fortunately, SQL provides TRUNCATE TABLE statement that allows you to remove all rows in a table more efficiently. The syntax of the TRUNCATE TABLE statement is as follows:

TRUNCATE TABLE table_name

Following the TRUNCATE TABLE keyword is the table name that you want to remove all the data. Unlike the DELETE statement, the TRUNCATE TABLE statement does not have WHERE clause.

SQL TRUNCATE TABLE Example

Let’s practice the TRUNCATE TABLE statement to get a better understanding.

First, we create a table named tmp with two columns id and name:

CREATE  TABLE tmp (
        id INT NOT NULL AUTO_INCREMENT ,
        name VARCHAR(45) NULL ,
        PRIMARY KEY (id) );

Second, we insert sample data using SQL INSERT statement:

INSERT INTO tmp(name) 
VALUES('MySQL');
INSERT INTO tmp(name)
VALUES('PostgreSQL');
INSERT INTO tmp(name)
VALUES('Oracle');
INSERT INTO tmp(name)
VALUES('Microsoft SQL Server')Code language: JavaScript (javascript)

Third, we query the data to check if the INSERT operation has been done successfully.

SELECT * FROM tmp;

Finally, we can use the SQL TRUNCATE TABLE statement to remove all rows in the tmp table.

TRUNCATE TABLE tmp

If we query the tmp table again, we will not see any data because the tmp table now is empty

The tmp table does not have a large number of rows so that we don’t see the difference in term of performance between the DELETE and TRUNCATE TABLE statements.

SQL TRUNCATE TABLE vs. SQL DELETE

Using SQL TRUNCATE TABLE statement brings several advantages when comparing with using the DELETE statement in some database management systems such as Microsoft SQL Server, Oracle, and MySQL.

• The SQL TRUNCATE TABLE statement uses less transaction log space because it removes all rows in a table at a time while the SQL DELETE statement removes one row at a time and also writes a transaction log for each deleted row.
• The SQL TRUNCATE TABLE statement uses table level lock while the SQL DELETE statement uses row-level lock, therefore, SQL TRUNCATE TABLE statement uses fewer locks than the SQL DELETE statement.
• The SQL TRUNCATE TABLE statement executes faster than the SQL DELETE statement does when removing all rows in a big table.
• If a table has an auto-increment column, the counter for that column will reset to its initial value, typically zero, defined in the column definition if you use SQL TRUNCATE TABLE.

SQL TRUNCATE TABLE limitations

However, the SQL TRUNCATE TABLE statement has some limitations:

• The SQL TRUNCATE TABLE statement cannot be used in a table referenced by a FOREIGN KEY constraint.
• In some RDBMSs, the SQL TRUNCATE TABLE statement cannot be used for a table that participates in an index view or table that uses in the replication scenario.

In this tutorial, you have learned how to use SQL TRUNCATE TABLE statement to remove all rows in a table.