Name
TRUNCATE TABLE Statement
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 up 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 |
MySQL | Supported |
Oracle | Supported |
PostgreSQL | Supported |
SQL Server | Supported |
De Facto Standard Syntax
Officially, TRUNCATE TABLE is not an ANSI-standard command. However, it is a commonly supported statement that follows this standard 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 some important differences: TRUNCATE TABLE is faster and it is non-logged, meaning it cannot be rolled back if issued in error, and 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
Because the TRUNCATE TABLE statement is not logged, ...
Get SQL in a Nutshell, 3rd Edition 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.