Ask the Expert

DROP versus TRUNCATE table

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

    Requires Free Membership to View

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.

This was first published in November 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: