Chapter 9 SQL Statements
631
TRUNCATE TABLE statement
Use this statement to delete all rows from a table, without deleting the table
definition.
TRUNCATE TABLE [
owner
.]
table-name
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.
Delete triggers are not fired by the TRUNCATE TABLE statement.
If TRUNCATE_WITH_AUTO_COMMIT is set to ON (the default), 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 client 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.
"DELETE statement" on page 496
"TRUNCATE_WITH_AUTO_COMMIT option" on page 215
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.
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.
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.
Function
Syntax
Permissions
Side effects
See also
Description
To Next Page
Loading...