Use this statement to delete all rows from a table without deleting the table definition.
TRUNCATE TABLE [ owner.]table-name | MATERIALIZED VIEW [ owner.]materialized-view-name
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 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:
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.
SQL/2003 Transact-SQL extension.
Delete all rows from the Departments table:
TRUNCATE TABLE Departments;
|Send feedback about this page via email or DocCommentXchange||Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0|