Search This Blog

Thursday, September 1, 2011

Difference between Delete, Truncate and Drop


What is the difference between Delete, Truncate and Drop ?
  • Delete is a DML statement
    • Truncate and Drop are DDL statements
  • Delete statement will fire a trigger.
    • Truncate and Drop will not fire the trigger
  • Delete requires rollback or commit to complete the transaction
    • Truncate and Drop do not require either rollback or commit
  • Delete will not reset the high water mark (reclaim extents)
    • Truncate will reset the high water mark and reclaim space (except first extent)
    • Drop will release space used by the object 
  • Delete and Truncate can only be used against a table.
    • Drop can be used against other object types too (for example views, procedure...)
  • Delete and Truncate will not remove (drop) the indexes or triggers associated with the table.
    • Drop will also remove (drop) all the indexes and triggers associated with the table.

No comments: