Skip to Main Content
SQL in a Nutshell
book

SQL in a Nutshell

by Kevin Kline
December 2000
Intermediate to advanced content levelIntermediate to advanced
224 pages
9h 52m
English
O'Reilly Media, Inc.
Content preview from SQL in a Nutshell

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

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

SQL in a Nutshell, 3rd Edition

SQL in a Nutshell, 3rd Edition

Kevin Kline

Publisher Resources

ISBN: 1565927443