Removes all rows from a table or partition.
truncate table [[database.]owner.]table_name [partition partition_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.
specifies the name of the partition to truncate.
Removes all data from the authors table:
truncate table authors
Removes all data from the smallsales partition of the titles table:
truncate table titles partition smallsales
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.
Adaptive 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.
ANSI SQL – Compliance level: Entry-level compliant.
The permission checks for truncate table differ based on your granular permissions settings.
Granular permissions 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. |
Granular permissions 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. |
Values in event and extrainfo columns of sysaudits are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
64 |
truncate |
truncate table |
|
Commands alter table, create table, create trigger, delete, drop table, grant, revoke
