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

Synopsis

The TRUNCATE TABLE command is a non-ANSI statement that removes all rows from a table without logging the individual row deletes. It is a very handy command because it quickly erases all the records in a table without altering the table structure, while taking very little space in the redo logs or transaction logs. However, it has a dark side; since it is not logged, it cannot be recovered or backed up.

Vendor

Command

SQL Server

Supported

MySQL

Not supported

Oracle

Supported

PostgreSQL

Supported

SQL99 Syntax and Description

TRUNCATE TABLE name

The TRUNCATE TABLE statement has the same effect 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 roll back if issued in error.

Typically, TRUNCATE TABLE does not activate triggers and does not function when foreign keys are in place on a given table.

Example

This example removes all data from the publishers table:

TRUNCATE TABLE publishers

Oracle Syntax and Variations

TRUNCATE { CLUSTER [owner.]cluster 
   | TABLE [owner.]table [{PRESERVE | PURGE} SNAPSHOT LOG]}
[{DROP | REUSE} STORAGE]

Oracle allows a table or an indexed cluster (but not a hash cluster) to be truncated.

When truncating a table, Oracle allows the option of preserving or purging the snapshot log, if one is defined on the table. PRESERVE maintains the snapshot log when the master table is truncated, while ...

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