SQLite Truncate Table

  Total Views : 396
  Comments : 0
  Keywords : sqlite truncate command with example, delete all the rows from table using sqlite truncate statement with example, sqlite reset index or auto increment with truncate command

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.

SQLite Truncate Table

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 DELETE command without WHERE clause we can achieve the same TRUNCATE functionality like to delete all the records from 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.

Syntax SQLite Truncate 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.

Example of SQLite Truncate Table

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.

 

sqliteSELECT 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 “Test” table using following command.

 

DELETE FROM TEST;

If you observe above statement we used DELETE command without WHERE clause so the TRUNCATE optimizer will fire and remove all the records of table.

 

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. 

 

PREVIOUS

SQLite Triggers
 

NEXT

SQLite Vacuum  
 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User