sql-truncate-table-command

SQL - TRUNCATE TABLE Command

The TRUNCATE TABLE command is a Data Definition Language (DDL) statement used to quickly and efficiently remove all rows from a table. While its effect is similar to a DELETE statement without a WHERE clause, its underlying mechanism is very different.

Unlike DROP TABLE, which deletes the table’s structure itself, TRUNCATE TABLE preserves the table structure, including columns, constraints, and indexes, making it ready for new data.

Syntax

The basic syntax for the TRUNCATE TABLE command is:

TRUNCATE TABLE table_name;

Some RDBMSs offer extended syntax. For instance, in PostgreSQL, you can also reset associated sequences and manage foreign key dependencies:

-- PostgreSQL Example
TRUNCATE TABLE table_name RESTART IDENTITY CASCADE;

Key Differences: TRUNCATE vs. DELETE

• Performance: TRUNCATE is significantly faster for large tables because it deallocates data pages instead of deleting rows one by one.
• Logging: TRUNCATE is minimally logged, whereas DELETE logs every single row deletion, which can generate a large amount of transaction log data.
• Triggers: TRUNCATE does not fire DELETE triggers in most databases. DELETE fires triggers for each affected row.
• Identity Columns: TRUNCATE resets identity (auto-increment) columns to their starting value. DELETE does not.
• Transactions: DELETE is a DML operation and can always be rolled back. TRUNCATE is a DDL operation; its ability to be rolled back varies by RDBMS (it can be in PostgreSQL and SQL Server, but it’s an implicit commit in older versions of MySQL).

Example

Consider a Logs table that accumulates a large number of records:

+----+------------+-----------------------------------+
| ID | LogLevel   | Message                           |
+----+------------+-----------------------------------+
|  1 | INFO       | User 'alice' logged in.           |
|  2 | INFO       | User 'bob' logged in.             |
|  3 | WARNING    | Disk space is running low.        |
|... | ...        | ... (millions of other rows)      |
+----+------------+-----------------------------------+

To clear out this table for a new period, using DELETE FROM Logs; would be very slow. The TRUNCATE command is the ideal solution:

TRUNCATE TABLE Logs;

After this command, the Logs table will be empty, and if its ID column is an identity/auto-increment column, the next record inserted will start back at 1 (or its original seed value).

SELECT * FROM Logs;
-- Returns: Empty set

When to Use TRUNCATE

Use TRUNCATE TABLE when you need to remove all data from a table, you don’t need to fire triggers, and performance is a primary concern. It is a common operation in data warehousing (ETL processes) and for resetting tables in testing or development environments.