The TRUNCATE statement deletes all rows from the table or materialized view.
The TRUNCATE TABLE statement should be used with great care on a database involved in synchronization or replication because
the 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 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
After a TRUNCATE statement, the object's schema and all the indexes continue to exist until you issue a DROP statement. The
schema definitions and constraints remain intact, and triggers and permissions remain in effect.
table-name can be the name of a base table or a temporary table.
With TRUNCATE TABLE, if all the following criteria are satisfied, a fast form of table truncation is executed:
There are no foreign keys either to or from the table.
The TRUNCATE TABLE statement is not executed within a trigger.
The TRUNCATE TABLE statement is not executed within an atomic statement.
If a fast truncation is carried out, individual DELETEs are not recorded in the transaction log, and a COMMIT is carried out
before and after the operation. Fast truncation cannot be used within snapshot transactions. See Snapshot isolation.
If you attempt to use TRUNCATE TABLE on a table on which an immediate text index is built, or that is referenced by an immediate
view, the truncation fails. This does not occur for non-immediate text indexes or materialized views; however, it is strongly
recommended that you truncate the data in dependent indexes and materialized views before executing the TRUNCATE TABLE statement
on a table, and then refreshing the indexes and materialized views after. See TRUNCATE statement, and TRUNCATE TEXT INDEX statement.
Must be the table owner, or have DBA authority, or have ALTER permissions on the table.
For base tables and materialized views, the TRUNCATE 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 when executing the TRUNCATE
Delete triggers are not fired by the TRUNCATE statement.
A COMMIT is performed before and after a TRUNCATE statement is executed.
Individual deletions of rows are not entered into the transaction log, so the TRUNCATE 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, the truncation operation
resets the next available value for the column.