TRUNCATE statement

Syntax

TRUNCATE 
TABLE [ owner.]table-name
| MATERIALIZED VIEW [ owner.]materialized-view-name

Remarks

The TRUNCATE statement deletes all rows from the table or materialized view.

Note The TRUNCATE TABLE statement should be used with great care on a database involved in synchronization or replication because the statement deletes all rows from a table, similar to a DELETE statement that doesn't have a WHERE clause. However, no triggers are fired as a result of a TRUNCATE statement. If the database server determines that a fast truncate is possible (described later in this topic), the row deletions are not entered into the transaction log and therefore are not synchronized or replicated. This can lead to inconsistencies that can cause synchronization or replication to fail.

After a TRUNCATE statement, the object's schema and all the indexes continue to exist until you execute a DROP statement. The schema definitions and constraints remain intact, and triggers and privileges remain in effect.

table-name can be the name of a base table or a temporary table.

With TRUNCATE TABLE, if all the following criteria are satisfied, the database server performs a fast form of table truncation (fast truncate):

There are no foreign keys either to or from the table.
The TRUNCATE TABLE statement is not executed within a trigger.
The TRUNCATE TABLE statement is not executed within an atomic statement.

If a fast truncation is carried out, individual DELETEs are not recorded in the transaction log, and a COMMIT is carried out before and after the operation. If a fast truncation is not possible, individual DELETES are recorded in the transaction log.

Fast truncation cannot be used within snapshot transactions.

If you attempt to use TRUNCATE TABLE on a table on which an immediate text index is built, or that is referenced by an immediate view, the truncation fails. This does not occur for non-immediate text indexes or materialized views; however, it is strongly recommended that you truncate the data in dependent indexes and materialized views before executing the TRUNCATE TABLE statement on a table, and then refreshing the indexes and materialized views after.

For base tables and materialized views, the TRUNCATE statement requires exclusive access to the table, as the operation is atomic (either all rows are deleted, or none are). Any cursors that were previously opened and that reference the table being truncated must be closed and a COMMIT or ROLLBACK must be executed to release the reference to the table.

For temporary tables, each user has their own copy of the data, and exclusive access is not required when executing the TRUNCATE statement.

Privileges

To execute this statement, one of the following conditions must be true:

You must be the owner of the table.
You have ALTER privilege on the table
You have TRUNCATE privilege on the table
You have the ALTER ANY TABLE system privilege
You have the TRUNCATE ANY TABLE system privilege
You have the ALTER ANY OBJECT system privilege

Side effects

When you truncate a materialized view, you change the status of the view to uninitialized.
Delete triggers are not fired by the TRUNCATE statement.
A COMMIT is performed before and after a TRUNCATE statement is executed.
Individual deletions of rows are not entered into the transaction log, so the TRUNCATE operation is not replicated. Do not use this statement in SQL Remote replication or on a MobiLink remote database.
If the table contains a column defined as DEFAULT AUTOINCREMENT or DEFAULT GLOBAL AUTOINCREMENT, the truncation operation resets the next available value for the column.

Standards

ANSI/ISO SQL Standard
The TRUNCATE TABLE statement is optional Language Feature F200. TRUNCATE MATERIALIZED VIEW is not in the standard.

TRUNCATE statement

Example