Reference Manual
   CHAPTER 9. SQL Statements     

TRUNCATE TABLE statement

Function 

To delete all rows from a table, without deleting the table definition.

Syntax 

TRUNCATE TABLE [ owner.]table-name

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

For temporary tables, each user has their own copy of the data, and exclusive access is not required.

Side effects 

Delete triggers are not fired by the TRUNCATE TABLE statement.

See also 

DELETE statement

Description 

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.

Standards and compatibility 

• SQL/92     Transact-SQL extension.

• Sybase     Supported by Adaptive Server Enterprise.

Example 

• Delete all rows from the department table.

  TRUNCATE TABLE department