TRUNCATE statement

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

Syntax

TRUNCATE 
TABLE  [ owner.]table-name
| MATERIALIZED VIEW [ owner.]materialized-view-name

Remarks

The TRUNCATE statement deletes all rows from the table or materialized view.

Note

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 to fail.

After a TRUNCATE statement, the object's schema and all of 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.

Permissions

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 statement.

Side effects

When you truncate a materialized view, you change the status of the view to uninitialized. See Materialized view statuses and properties.
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.

Standards and compatibility

SQL/2003 Transact-SQL extension.

Example

Delete all rows from the Departments table:

TRUNCATE TABLE Departments;

Send feedback about this page via email or DocCommentXchange