Problem solve Get help with specific problems with your technologies, process and projects.

DROP versus TRUNCATE table

What is the difference between the drop and truncate table commands?

Drop <table> drops the table, including the table definition and the associated objects (rules, indexes, constraints, triggers, primary key and so on). Obviously once a table is dropped; all the data rows contained in the table are also removed.

A truncate <table> command removes all rows from a table. The table structure and all the indexes continue to exist until you issue a drop table command (as discussed above). The rules, defaults and constraints that are bound to the columns remain bound, and triggers remain in effect.

Truncate table command also de-allocates the distribution pages for all indexes.

The 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 de-allocates whole data pages and makes fewer log entries. Both delete and truncate table reclaim the space occupied by the data and its associated indexes. Only the table owner can truncate a table.

Dig Deeper on Linux servers

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.