Here we will learn SQLite TRUNCATE TABLE command with example and how to use SQLite TRUNCATE statement to clear or delete all the table records with example.
Generally, in other RDMS applications we use TRUNCATE TABLE command to remove all the records from table completely but in SQLite, we don’t have TRUNCATE TABLE command to delete the table records and to reset auto increment or index of table.
In SQLite by using the DELETE command without WHERE clause we can achieve the same TRUNCATE functionality like to delete all the records from the table.
Generally, in SQLite if we use the DELETE command it will visit each and every row of table for deletion but when we use the DELETE command without WHERE clause it will truncate all the data from table without visiting each and every row of the table.
Following is the syntax to TRUNCATE table in SQLite.
DELETE FROM table_name
Here table_name is a valid name of the table that we want to truncate.
Now we will see how to truncate the table in SQLite with example for that create table “Test” and insert some data like as shown below.
sqlite> SELECT * FROM TEST;
id value
---------- ----------
1 Shweta
2 Vinay
3 Lax
4 Nidhi
5 Garima
6 Kittu
7 Kripa
8 Manisha
9 Soni
10 Jinal
Now, we will try to truncate the “Test” table using the following command.
DELETE FROM TEST;
If you observe above statement we used the DELETE command without WHERE clause so the TRUNCATE optimizer will fire and remove all the records of the table.
In SQLite TRUNCATE optimizer (Removing all the records from a table without visiting each and every row) will not work in case if the table is associated with TRIGGER instead it will visit each and row of table to delete records in the table.
Now, we will try to TRUNCATE table which is associated with a trigger and see what will happen.
We have a table called Product which is associated with the trigger so we will try to truncate Product table using following statement.
DELETE FROM Product;
Here as we know the Product table is associated with the trigger, so the TRUNCATE optimizer will not run to remove the data. Instead, it will visit each and every row of the table and do the removal process. It is normally a slow process than truncate.
This is how we can use TRUNCATE table records in SQLite based on our requirements.