TRUNCATE TABLE

Scope

SQL Syntax

  TRUNCATE TABLE [Owner.]TableName

Parameters

[Owner.]TableName

identifies the table whose rows are deleted.

Description

• Use the TRUNCATE TABLE when you want to delete all rows from a table, yet leave the table's structure intact. The TRUNCATE TABLE statement is faster than the DELETE statement and generates fewer log records.

• The table definition is not removed or modified. All indexes, views, constraints, rules, default values, and authorizations defined for the table are unchanged.

• The DBEFile space occupied by the table cannot be reused until the transaction ends.

• The DDL (data definition language) flag must be set to YES. Use the ALTDBE command in SQLUtil to set the DDL flag.

• If audit logging is enabled and the DEFINITION AUDIT ELEMENT option is specified, then audit log records are generated.

• If a constraint violation occurs when constraint checking is deferred, a warning is generated and the rows are deleted unless the transaction is explicitly rolled back. Should the violation occur when constraint checking is set to immediate, an error is generated and the rows are not deleted.

• All sections that access the specified table are invalidated.

• If the table is specified by a referential constraint, it may be more efficient to drop the constraint, issue the TRUNCATE TABLE statement, and specify the constraint again.

• Rules are automatically disabled during execution of the TRUNCATE TABLE statement.

Authorization

Example

   TRUNCATE TABLE PurchDB.Parts