SQL Truncate Explained

In SQL databases, the TRUNCATE statement can be used to quickly remove all data from a table. Unlike the DELETE statement, TRUNCATE is typically faster and uses fewer system resources, especially for large tables.

In this article, we’ll explore the TRUNCATE statement, its usage, and provide some examples.

Understanding `TRUNCATE`

TRUNCATE is a Data Definition Language (DDL) statement that removes all rows from a table. It’s similar to a DELETE statement without a WHERE clause, but with some key differences:

TRUNCATE is generally faster than DELETE.
TRUNCATE resets identity/auto increment columns (if any) to their seed value.
TRUNCATE doesn’t trigger DELETE triggers (although some RDBMSs may support TRUNCATE triggers).
TRUNCATE is not logged individually (which could have implications if you need to roll back).

The TRUNCATE statement is also known as the TRUNCATE TABLE statement, given it’s purpose of clearing all data from a table.

Syntax

The basic syntax for TRUNCATE goes like this:

TRUNCATE TABLE table_name;

Although some RDBMSs (such as PostgreSQL) allow multiple tables to be truncated at once:

TRUNCATE TABLE table_name_1, table_name_2, ...;

The exact syntax will depend on the RDMS in question (PostgreSQL accepts various arguments, for example), but that’s generally how the TRUNCATE statement works.

Example

Let’s create a table and populate it with data:

-- Create a sample table
CREATE TABLE magical_creatures (
    id INT IDENTITY(1,1) PRIMARY KEY,
    creature_name VARCHAR(50),
    habitat VARCHAR(50),
    magic_power VARCHAR(50),
    age INT,
    rarity_score DECIMAL(5, 2)
);

-- Insert sample data
INSERT INTO magical_creatures (creature_name, habitat, magic_power, age, rarity_score)
VALUES 
    ('Sparkle Unicorn', 'Rainbow Forest', 'Glitter Blast', 342, 8.7),
    ('Grumpy Gnome', 'Mushroom Village', 'Sarcasm Spell', 127, 3.2),
    ('Floating Jellyfish', 'Sky Ocean', 'Antigravity Bubbles', 56, 7.5),
    ('Lava Salamander', 'Volcanic Caves', 'Magma Manipulation', 789, 9.1),
    ('Whispering Willow', 'Enchanted Grove', 'Telepathic Leaves', 1203, 6.8),
    ('Thunderbird', 'Storm Clouds', 'Lightning Strike', 501, 8.9),
    ('Frost Phoenix', 'Glacial Peaks', 'Ice Resurrection', 277, 9.3),
    ('Bubblegum Kraken', 'Candy Sea', 'Sticky Tentacles', 892, 7.7),
    ('Shadow Cat', 'Twilight Realm', 'Invisibility Purr', 99, 8.2),
    ('Melodic Mushroom', 'Harmonic Caverns', 'Spore Symphony', 34, 5.6);

-- View the data
SELECT * FROM magical_creatures;

Result:

id  creature_name       habitat           magic_power          age   rarity_score
--  ------------------  ----------------  -------------------  ----  ------------
1   Sparkle Unicorn     Rainbow Forest    Glitter Blast        342   8.7         
2   Grumpy Gnome        Mushroom Village  Sarcasm Spell        127   3.2         
3   Floating Jellyfish  Sky Ocean         Antigravity Bubbles  56    7.5         
4   Lava Salamander     Volcanic Caves    Magma Manipulation   789   9.1         
5   Whispering Willow   Enchanted Grove   Telepathic Leaves    1203  6.8         
6   Thunderbird         Storm Clouds      Lightning Strike     501   8.9         
7   Frost Phoenix       Glacial Peaks     Ice Resurrection     277   9.3         
8   Bubblegum Kraken    Candy Sea         Sticky Tentacles     892   7.7         
9   Shadow Cat          Twilight Realm    Invisibility Purr    99    8.2         
10  Melodic Mushroom    Harmonic Caverns  Spore Symphony       34    5.6

The above code works in SQL Server. Other RDBMs may have a slightly different syntax for the CREATE TABLE statement. For example, in MySQL we might use an AUTO_INCREMENT or a SERIAL column instead of the IDENTITY column.

Anyway, now that we have our whimsical table with data, let’s use the TRUNCATE command against it:

-- Truncate the magical_creatures table
TRUNCATE TABLE magical_creatures;

-- View the data after truncation
SELECT * FROM magical_creatures;

Result:

0 row(s) returned

After running the TRUNCATE statement, we can see that all rows have been removed from the table.

It’s important to note that the table still exists, and its structure remains intact.

`TRUNCATE` vs. `DELETE`

To illustrate the difference between TRUNCATE and DELETE, let’s repopulate our table and compare the two commands:

-- Repopulate the table
INSERT INTO magical_creatures (creature_name, habitat, magic_power, age, rarity_score)
VALUES 
    ('Sparkle Unicorn', 'Rainbow Forest', 'Glitter Blast', 342, 8.7),
    ('Grumpy Gnome', 'Mushroom Village', 'Sarcasm Spell', 127, 3.2),
    ('Floating Jellyfish', 'Sky Ocean', 'Antigravity Bubbles', 56, 7.5);

-- Use DELETE to remove all rows
DELETE FROM magical_creatures;

-- Insert a new creature
INSERT INTO magical_creatures (creature_name, habitat, magic_power, age, rarity_score)
VALUES ('Disco Dragon', 'Funky Cavern', 'Groovy Flames', 178, 7.9);

-- View the data
SELECT * FROM magical_creatures;

Result:

id  creature_name  habitat       magic_power    age  rarity_score
--  -------------  ------------  -------------  ---  ------------
4   Disco Dragon   Funky Cavern  Groovy Flames  178  7.9

We can see that the new row has an id of 4, as DELETE doesn’t reset the identity column.

Now, let’s try with TRUNCATE:

-- Repopulate the table
INSERT INTO magical_creatures (creature_name, habitat, magic_power, age, rarity_score)
VALUES 
    ('Sparkle Unicorn', 'Rainbow Forest', 'Glitter Blast', 342, 8.7),
    ('Grumpy Gnome', 'Mushroom Village', 'Sarcasm Spell', 127, 3.2),
    ('Floating Jellyfish', 'Sky Ocean', 'Antigravity Bubbles', 56, 7.5);

-- Use TRUNCATE to remove all rows
TRUNCATE TABLE magical_creatures;

-- Insert a new creature
INSERT INTO magical_creatures (creature_name, habitat, magic_power, age, rarity_score)
VALUES ('Pixie Dust Panda', 'Bamboo Dreamland', 'Wish Granting', 88, 8.5);

-- View the data
SELECT * FROM magical_creatures;

Result:

id  creature_name     habitat           magic_power    age  rarity_score
--  ----------------  ----------------  -------------  ---  ------------
1   Pixie Dust Panda  Bamboo Dreamland  Wish Granting  88   8.5

This time, we can see that the new row has an id of 1, as TRUNCATE resets the identity column.

SQLite

SQLite doesn’t have a TRUNCATE or TRUNCATE TABLE statement, but it does have a thing called “truncate optimization”. Truncate optimization works like this:

A default build of SQLite, if a DELETE statement has no WHERE clause and operates on a table with no triggers, an optimization occurs that causes the DELETE to occur by dropping and recreating the table. Dropping and recreating a table is usually much faster than deleting the table content row by row.

See the SQLite documentation for the DELETE statement for more information.

Considerations

TRUNCATE typically requires different permissions/privileges to the DELETE statement. For example, in SQL Server it requires at least ALTER permissions on the table. MySQL requires DROP privileges, and PostgreSQL requires TRUNCATE privileges.
In some RDBMSs, TRUNCATE cannot be used with tables that are referenced by foreign key constraints (unless the foreign key is disabled or not enforced). In others (such as PostgreSQL), we can control whether or not to truncate related tables.
TRUNCATE may not be available for all database systems or table types (e.g., some view-based tables).

Understanding TRUNCATE

Syntax

Example

TRUNCATE vs. DELETE

SQLite

Considerations

Understanding `TRUNCATE`

`TRUNCATE` vs. `DELETE`