Difference between Truncate, Delete and Drop in SQL

TRUNCATE

This operation doesn’t affect the table schema.

Advantages when compared to Delete operation:

  • Faster and uses less resources.
  • Doesn’t log each row that’s deleted.
  • Requires fewer locks – locks are obtained for the whole table and the page but not on each row.
  • If table contains an identity column, the corresponding counter is reset to the seed value for the column.
  • After truncate operation is completed, zero pages are left in the table without any exception.
  • After truncate, the pages can be immediately reused in other areas.

Disadvantages:

  • Doesn’t allow WHERE condition.
  • Cannot be used if table has foreign key constraints.
  • Cannot be used if the table participates in an indexed view.
  • If the table is published using transactional replication or merge replication.
  • Cannot activate triggers because row level locks are not used. On Update,Delete,Insert triggers won’t fire
  • Rows deleted cannot be restored.

DELETE

This operation doesn’t affect the schema.

DELETE FROM TABLE achieves the same result as Truncate but in a less efficient way.

Advantages

  • Locks each individual row for deletion and so rows deleted can be restored if needed.
  • Logs each row that has been deleted.
  • Retains the counter value for Identity column.
  • Allows WHERE condition.

Disadvantages

  • Slow and uses more resources
  • Empty pages cannot be reused until a background cleanup process de-allocates the pages.

DROP

Deletes the data as well as the schema.

  • Cannot be used on a table with foreign key constraint


Advertisements

One thought on “Difference between Truncate, Delete and Drop in SQL”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s