SQL in a Nutshell, 3rd Edition

Name

TRUNCATE TABLE Statement

The TRUNCATE TABLE statement, a non-ANSI statement, irrevocably removes all rows from a table without logging the individual row deletes. It quickly erases all the records in a table without altering the table structure, taking up little or no space in the redo logs or transaction logs. However, since a truncate operation is not logged, the TRUNCATE TABLE statement cannot be rolled back once it is issued.

Platform	Command
MySQL	Supported
Oracle	Supported
PostgreSQL	Supported
SQL Server	Supported

De Facto Standard Syntax

Officially, TRUNCATE TABLE is not an ANSI-standard command. However, it is a commonly supported statement that follows this standard format:

TRUNCATE TABLE table_name

Keywords

table_name

The name of any valid table within the current database or schema context.

Rules at a Glance

The TRUNCATE TABLE statement has the same effect on a table as a DELETE statement with no WHERE clause; both erase all rows in a given table. However, there are some important differences: TRUNCATE TABLE is faster and it is non-logged, meaning it cannot be rolled back if issued in error, and TRUNCATE TABLE does not activate triggers, while the DELETE statement does.

This command should be issued manually. We strongly encourage you not to place it into automated scripts or production systems that contain irreplaceable data. It cannot be paired with transaction control statements such as BEGIN TRAN or COMMIT.

Programming Tips and Gotchas

Because the TRUNCATE TABLE statement is not logged, ...