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 ...
Get SQL in a Nutshell now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.