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.
Generally, in SQLite if we use DELETE command it will visit each and every row of table for deletion but when we use DELETE command without WHERE clause it will truncate all the data from table without visiting each and every row of 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 table in SQLite with example for that create table “Test” and insert some data like as shown below.
sqlite> SELECT * FROM TEST;
Now, we will try to truncate “Test” table using following command.
DELETE FROM TEST;
In SQLite TRUNCATE optimizer (Removing all the records from 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 table.
Now, we will try to TRUNCATE table which is associated with trigger and see what will happened.
We have table called Product which is associated with 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 trigger, so the TRUNCATE optimizer will not run to remove the data, instead it will visit each and every row of table and done the removal process. It is normally slow process than truncate.
This is how we can use TRUNCATE table records in SQLite based on our requirements.