SQL TRUNCATE

TRUNCATE is a SQL statement used to quickly delete all rows from a table while retaining the table structure. Unlike the DELETE statement, TRUNCATE does not log individual row deletions and does not generate triggers, making it faster for large datasets.

Key Characteristics

• Performance: TRUNCATE is more efficient than DELETE because it uses minimal logging.
• Data Integrity: It resets identity columns (e.g., AUTO_INCREMENT) to their seed values.
• Irreversibility: TRUNCATE operations cannot be rolled back in most database systems, as it does not log row-level changes.
• Constraints: Cannot truncate a table that is referenced by a foreign key.

Comparison: TRUNCATE vs. DELETE

Syntax

TRUNCATE TABLE table_name;

• table_name: Specifies the name of the table to truncate.

Note: Use TRUNCATE carefully in production environments, as it cannot be undone.

Example

The TRUNCATE statement can be used to clear all records from a table while retaining its structure. For instance, to remove all data from a table named employee_data:

TRUNCATE TABLE employee_data;

Copy to clipboard
Copy to clipboard

This removes all rows from the employee_data table without affecting the table’s schema or structure.