truncate table

Syntax

truncate table [[database.]owner.]table_name 
	[partition partition_name]

Parameters

table_name – is the name of the table to truncate. Specify the database name if the table is in another database, and specify the owner’s name if more than one table of that name exists in the database. The default value for owner is the current user, and the default value for database is the current database.
partition_name – specifies the name of the partition to truncate.

Examples

Example 1 – Removes all data from the authors table:
```
truncate table authors
```
Example 2 – Removes all data from the smallsales partition of the titles table:
```
truncate table titles partition smallsales
```

Usage

truncate table deletes all rows from a table. The table structure and all the indexes continue to exist until you issue a drop table command. The rules, defaults, and constraints that are bound to the columns remain bound, and triggers remain in effect.
The SAP ASE server no longer uses distribution pages; statistical information is now stored in the tables sysstatistics and systabstats.

During truncate table, statistical information is no longer deleted (deallocated), so you need not run update statistics after adding data.

truncate table does not delete statistical information for the table.
truncate table is equivalent to—but faster than—a delete command without a where clause. delete removes rows one at a time and logs each deleted row as a transaction; truncate table deallocates whole data pages and makes fewer log entries. Both delete and truncate table reclaim the space occupied by the data and its associated indexes.
Truncating a partition does not affect the data in other partitions.
You can truncate only one partition at a time.
Truncating a table locks the entire table until the truncation process is complete.
Because the deleted rows are not logged individually, truncate table cannot fire a trigger.
You cannot use truncate table if another table has rows that reference it. Delete the rows from the foreign table, or truncate the foreign table, then truncate the primary table.
You can grant and revoke permissions to users and roles to use truncate table on tables with the grant and revoke commands.

Standards

ANSI SQL – Compliance level: Entry-level compliant.

Permissions

The permission checks for truncate table differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must be the table owner or a user with `truncate table` permission on the table. To truncate an auditing table, you must have `manage auditing` privilege or `truncate any audit table` privilege.
Disabled	With granular permissions disabled, you must be the table owner, a user with truncate table permission on the table, a user with replication_role, or a user with sa_role.To truncate an auditing table, you must be a user with sso_role.

Auditing

Values in event and extrainfo columns of sysaudits are:

Information	Values
Event	64
Audit option	truncate
Command or access audited	truncate table
Information in `extrainfo`	Roles – current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – NULL Proxy information – original login name, if set proxy is in effect