Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » SQL Statements

TRIGGER EVENT statement Next Page

TRUNCATE TABLE statement


Use this statement to delete all rows from a table, without deleting the table definition.

Syntax

TRUNCATE TABLE [ owner.]table-name

Remarks

The TRUNCATE TABLE statement deletes all rows from a table. It is equivalent to a DELETE statement without a WHERE clause, except that no triggers are fired as a result of the TRUNCATE TABLE statement and each individual row deletion is not entered into the transaction log.

Note

This statement should be used with great care on a database involved in synchronization or replication. The TRUNCATE TABLE 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 TABLE statement. Furthermore, 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 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 triggers and permissions remain in effect.

Note

The TRUNCATE TABLE 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.

TRUNCATE TABLE statements are not replicated or synchronized by dbmlsync, dbremote, and dbltm. Only DELETE statements are replicated or synchronized.

If the truncate_with_auto_commit option is set to On (the default), and all the following criteria are satisfied, a fast form of table truncation is executed:

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

Permissions

Must be the table owner, or have DBA authority, or have ALTER permissions on the table.

For base tables, the TRUNCATE TABLE statement requires exclusive access to the table, as the operation is atomic (either all rows are deleted, or none are). This means that any cursors that were previously opened and that reference the table being truncated must be closed and a COMMIT or ROLLBACK must be issued 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.

If a fast truncation is performed, TRUNCATE TABLE cannot be used within snapshot transactions. See Snapshot isolation.

Side effects

Delete triggers are not fired by the TRUNCATE TABLE statement.

If truncate_with_auto_commit is set to On, then a COMMIT is performed before and after the table is truncated.

Individual deletions of rows are not entered into the transaction log, so the TRUNCATE TABLE 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, TRUNCATE TABLE resets the next available value for the column.

See also
Standards and compatibility
Example

Delete all rows from the Departments table.

TRUNCATE TABLE Departments;