SQLite - TRUNCATE Command

SQLite - How to Truncate a Table

Unlike some other SQL databases, SQLite does not have a dedicated TRUNCATE TABLE command. However, you can achieve the same goal—deleting all rows from a table—using the standard DELETE command. For modern SQLite versions, this operation is highly optimized.

Primary Method: Using DELETE

The standard and safest way to remove all rows from a table is with the DELETE statement, without a WHERE clause.

-- Deletes all rows from the specified table
DELETE FROM table_name;

Modern SQLite versions include a “truncate optimization.” When the DELETE statement has no WHERE clause and the table has no triggers, SQLite uses a more efficient internal mechanism to erase the entire table content, which is significantly faster than deleting rows one by one.

DELETE vs. DROP TABLE

It’s important to understand the difference between deleting rows and dropping the table entirely.

• DELETE FROM table_name;: This is a Data Manipulation Language (DML) command. It removes all rows but keeps the table structure, including its schema, indexes, and constraints. This operation can be rolled back within a transaction. It does not reset AUTOINCREMENT counters on its own.
• DROP TABLE table_name;: This is a Data Definition Language (DDL) command. It completely removes the table and all its data, indexes, and schema from the database. To use the table again, you must CREATE it from scratch. This is not typically what you want when you mean “truncate”.

Resetting AUTOINCREMENT Counters

A common reason developers look for TRUNCATE is to reset an AUTOINCREMENT primary key column (often INTEGER PRIMARY KEY). After a DELETE, the sequence continues from where it left off. To reset it, you must perform an additional step on the internal sqlite_sequence table.

-- Step 1: Delete all data from the table
DELETE FROM COMPANY;

-- Step 2: Reset the autoincrement counter for the COMPANY table
DELETE FROM sqlite_sequence WHERE name = 'COMPANY';

Note: The sqlite_sequence table only contains entries for tables that have actually used their AUTOINCREMENT feature. If you get 0 rows affected, that is normal.

Reclaiming Unused Space with VACUUM

After deleting a large amount of data, the database file may contain empty pages, resulting in unused disk space. The VACUUM command rebuilds the entire database file, which cleans up this fragmentation and reduces the file size.

-- Recommended after large-scale deletions to reclaim disk space
VACUUM;

Complete Example

Let’s assume we have a COMPANY table. Here is the complete workflow to clear the table, reset its primary key sequence, and reclaim disk space.

-- Original state of the table
SELECT * FROM COMPANY;
-- ID | NAME  | AGE | ADDRESS    | SALARY
-- ---|-------|-----|------------|--------
-- 1  | Paul  | 32  | California | 20000.0
-- 2  | Allen | 25  | Texas      | 15000.0

-- Start a transaction for safety
BEGIN TRANSACTION;

-- Remove all rows from the table
DELETE FROM COMPANY;

-- Reset the autoincrement counter
DELETE FROM sqlite_sequence WHERE name = 'COMPANY';

-- Commit the changes
COMMIT;

-- Reclaim unused space (optional, runs outside a transaction)
VACUUM;

-- Now, the COMPANY table is empty and any new entry will start with ID 1.
SELECT * FROM COMPANY;
-- (no rows returned)