truncate table

Removes all rows from a table or partition.

Syntax

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

Parameters

Examples

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.

SettingDescription
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:

InformationValues
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

Related reference
alter table
create table
create trigger
delete
drop table
grant
revoke