SQL in a Nutshell, 2nd Edition

Name

TRUNCATE TABLE Statement

Synopsis

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 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
DB2	Not supported
MySQL	Supported
Oracle	Supported
PostgreSQL	Supported
SQL Server	Supported

Defacto Standard Syntax

Officially, TRUNCATE TABLE is not an ANSI standard command. However, it is a commonly supported statement standard that follows this 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 two important differences. TRUNCATE TABLE is faster and it is non-logged, meaning it cannot be rolled back if issued in error. Plus, 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 ...