O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required