TRUNCATE Statement

Syntax

Syntax 1

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

Syntax 2

TRUNCATE TABLE  [ owner .]table  
 [ PARTITION partition-name 
    | SUBPARTITION subpartition-name ]

Examples

Example 1 – Delete all rows from the Sale table:
```
TRUNCATE TABLE Sale
```

Usage

TRUNCATE is equivalent to a DELETE statement without a WHERE clause, except that each individual row deletion is not entered into the transaction log. After a TRUNCATE TABLE statement, the table structure and all of the indexes continue to exist until you issue a DROP TABLE statement. The column definitions and constraints remain intact, and permissions remain in effect.

The TRUNCATE statement is entered into the transaction log as a single statement, like data definition statements. Each deleted row is not entered into the transaction log.

The PARTITION clause specifies which partition to truncate, and does not affect data in other partitions. Use SUBPARTITION to truncate tables partitioned by a composite partitioning scheme.

Note: Specifying an RLV-enabled table in the PARTITION or SUBPARTITION clause results in an error.

Standards

SQL—Vendor extension to ISO/ANSI SQL grammar.
Sybase—Supported by Adaptive Server Enterprise.

Permissions

Requires one of:

TRUNCATE ANY TABLE system privilege.
ALTER ANY TABLE system privilege.
ALTER ANY OBJECT system privilege.
TRUNCATE privilege on the table.
You own the object.

For both temporary and base tables, you can execute TRUNCATE TABLE while other users have read access to the table. This behavior differs from SQL Anywhere, which requires exclusive access to truncate a base table. SAP Sybase IQ table versioning ensures that TRUNCATE TABLE can occur while other users have read access; however, the version of the table these users see depends on when the read and write transactions commit.