TRUNCATE table in SQL

SQL

TRUNCATE table statement is used to quickly and efficiently remove all rows from a table. Unlike the DELETE statement, which removes rows one by one and logs each deletion, TRUNCATE operates more efficiently by deallocating entire data pages at once.

Syntax

The syntax for the TRUNCATE TABLE statement is straightforward:

TRUNCATE TABLE table_name;
  • table_name: The name of the table from which you want to remove all rows.

Key Points

  1. Speed: TRUNCATE is generally faster than the DELETE statement, especially for large tables, as it doesn’t log individual row deletions.
  2. Reset Identity Columns: If your table has an identity column (auto-incrementing), TRUNCATE resets the identity counter to its seed value.
  3. Transaction Log: Unlike DELETE, TRUNCATE doesn’t log individual row deletions. This can result in less transaction log space being used.
  4. Table Structure: The TRUNCATE statement keeps the table structure intact. It removes all rows from the table but retains the columns, constraints, indexes, and triggers.

Practical Example

Let’s say we want to remove all records from the Students table:

TRUNCATE TABLE Students;

Output:

Query OK, 0 rows affected

This query efficiently deletes all rows from the Students table, leaving an empty but intact table structure.

Note: No WHERE Clause. Unlike the DELETE statement, TRUNCATE doesn’t support a WHERE clause. It removes all rows from the table.