TRUNCATE TABLE

The TRUNCATE TABLE statement is similar to a DELETE statement that deletes all rows. However, it is faster than DELETE in most circumstances, as DELETE removes each row individually.

Required privilege

No privilege is required for the table owner.

DELETE for another user's table.

SQL syntax

TRUNCATE TABLE [Owner.]TableName

Parameters

The TRUNCATE TABLE has the parameter:

Parameter	Description
`[Owner.]TableName`	Identifies a table to be truncated.

Description

TRUNCATE is a DDL statement and thus is controlled by the DDLCommitBehavior attribute. If DDLCommitBehavior=0 (the default), then a commit is performed before and after execution of the TRUNCATE statement. If DDLCommitBehavior=1, then TRUNCATE is part of a transaction and these transactional rules apply:
- TRUNCATE operations can be rolled back.
- Subsequent INSERT statements are not allowed in the same transaction as a TRUNCATE statement.
Concurrent read committed read operations are allowed, and semantics of the reads are the same as for read committed reads in presence of DELETE statements
TRUNCATE is allowed even when there are child tables. However, child tables need to be empty for TRUNCATE to proceed. If any of the child tables have any rows in them, TimesTen returns an error indicating that a child table is not empty.
TRUNCATE is not supported with detail tables of a materialized view and a table that is a part of a cache group or a temporary table.
When a table contains out-of-line varying-length data, the performance of TRUNCATE TABLE is similar to that of DELETE statement that deletes all rows in a table. For more details on out-of line data, see "Numeric data types".
Where tables are being replicated, the TRUNCATE statement replicates to the subscriber, even when no rows are operated upon.
When tables are being replicated with timestamp conflict checking enabled, conflicts are not reported.
DROP TABLE and ALTER TABLE operations cannot be used to change hash pages on uncommitted truncated tables.

Examples

To delete all the rows from the recreation.clubs table, use:

TRUNCATE TABLE recreation.clubs;