ALLBASE/SQL Reference Manual (36216-90216)

578 Chapter12
SQL Statements S - Z
TRUNCATE TABLE
TRUNCATE TABLE
The TRUNCATE TABLE statement deletes all rows from the specified table.
Scope
ISQL, Application Programs, or Stored Procedures
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
You can issue this statement if you have OWNER authority for the table or if you have
DBA authority.