SQLite: TRUNCATE TABLE Statement

This SQLite post explains how to use TRUNCATE TABLE in SQLite with syntax and examples.

Description

The TRUNCATE TABLE statement is used to remove all records from a table.

SQLite does not have an explicit TRUNCATE TABLE command like other databases. Instead, it has added a TRUNCATE optimizer to the DELETE statement. To truncate a table in SQLite, you just need to execute a DELETE statement without a WHERE clause. The TRUNCATE optimizer handles the rest. Let's explain.

Normally, when you execute a DELETE statement, the database must visit each row in the table to perform the deletion. In SQLite, when you execute a DELETE statement without a WHERE clause, the TRUNCATE optimizer is run instead of the normal delete behavior. The TRUNCATE optimizer removes all data from the table without the need to visit each row in the table. This is much faster than a normal delete operation.

Syntax

The syntax to truncate a table in SQLite (using the TRUNCATE optimizer) is:

DELETE FROM table_name;

Parameters or Arguments

table_name

The table that you wish to truncate.

Note

• SQLite can only use the TRUNCATE optimizer if the table (to truncate) does not have any triggers.

Example

You might choose to truncate a table instead of dropping the table and recreating it. Truncating a table is a faster method of removing all data from the table.

Let's look at an example of how to truncate a table in SQLite.

For example:

DELETE FROM positions;

This example would truncate the table called positions and remove all records from that table. If there are no triggers on the positions table, SQLite will use the TRUNCATE optimizer to truncate the table. The data is then removed without having to visit each row of the positions table.

Let's finish with one more example.

DELETE FROM suppliers;

In this example, the suppliers table would be truncated and all data in the table would be removed.