TRUNCATE TABLE (Transact-SQL)

Removes all rows from a table quickly, but preserves the table structure. This is faster than DELETE without a WHERE clause.

Syntax

TRUNCATE TABLE table_name [ WITH ( options [ , ... ] ) ]

Where options can be:

  • FILESTREAM_ குறைக்க = { ON | OFF }
  • EMPTYFILE
  • NOCONSERVE_SPACE

Description

TRUNCATE TABLE is used to remove all rows from a table. It is more efficient than the DELETE statement without a WHERE clause. TRUNCATE TABLE is minimally logged and uses the space deallocation mechanism. This means it is faster and uses fewer system resources than deleting rows one by one.

Key characteristics of TRUNCATE TABLE:

  • It removes all rows from the table.
  • It is faster and uses fewer resources than DELETE without a WHERE clause.
  • It cannot be used on tables referenced by FOREIGN KEY constraints, unless the constraint is dropped or disabled first.
  • It cannot be used on tables that are part of replication or other system-level operations where row-by-row processing is required.
  • It resets identity columns to their seed values.
  • It is a Data Definition Language (DDL) operation, not a Data Manipulation Language (DML) operation.

Permissions

TRUNCATE TABLE requires ALTER permission on the table. By default, ALTER permission is held by the table owner, schema owner, and sysadmin role members.

Restrictions

  • TRUNCATE TABLE cannot be used if the table is referenced by a FOREIGN KEY constraint, or if it is subject to an INSERT or UPDATE trigger.
  • TRUNCATE TABLE cannot be used on tables that are part of a replication configuration or an indexed view.
  • TRUNCATE TABLE cannot be used on a table with a PRIMARY KEY constraint that is referenced by a FOREIGN KEY constraint in another table.

Usage Examples

Example 1: Truncating a simple table

TRUNCATE TABLE Sales.SalesOrderDetail;

This statement removes all rows from the Sales.SalesOrderDetail table.

Example 2: Truncating a table with FILESTREAM data

TRUNCATE TABLE dbo.MyDocs
WITH (FILESTREAM_ குறைக்க = ON);

This statement truncates the dbo.MyDocs table, which contains FILESTREAM data. The FILESTREAM_ குறைக்க option ensures that the FILESTREAM data is also purged.

Comparison with DELETE

While both TRUNCATE TABLE and DELETE can remove all rows from a table, they differ significantly:

  • Logging: DELETE logs each row deletion, making it slower and more resource-intensive. TRUNCATE TABLE is minimally logged.
  • Transaction Log: DELETE can be rolled back and operations within it are logged. TRUNCATE TABLE can be rolled back, but it's a faster operation.
  • Triggers: DELETE fires DELETE triggers. TRUNCATE TABLE does not fire triggers.
  • Identity Reset: DELETE does not reset identity values. TRUNCATE TABLE resets identity values to their seed.
  • Where Clause: DELETE can use a WHERE clause to remove specific rows. TRUNCATE TABLE removes all rows.
Tip: Use TRUNCATE TABLE when you need to empty a table quickly and do not need to preserve individual row information or fire triggers.
Note: Before truncating a table, ensure you have appropriate backups and understand the implications for referential integrity and any active transactions.
Important: TRUNCATE TABLE is a destructive operation. It cannot be undone easily if you do not have a backup.