Removes all rows from a table.
truncate table [[database.]owner.]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.
Removes all data from the authors table:
truncate table authors
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.
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 cannot use the truncate table command on a partitioned table. Unpartition the table with the unpartition clause of the alter table command before issuing the truncate table command.
You can use the delete command without a where clause to remove all rows from a partitioned table without first unpartitioning it. This method is generally slower than truncate table, since it deletes one row at a time and logs each delete operation.
SQL92 – Compliance level: Entry-level compliant.
truncate table permission defaults to the table owner and is not transferable. To truncate a system audit table (sysaudits_01, sysaudits_02, sysaudits_03, and so on, through sysaudits_08), you must be a System Security Officer.
Commands create trigger, delete, drop table
