Difference between TRUNCATE, DELETE and DROP commands

  1. TRUNCATE is a Data Definition Language command whereas DELETE is a Data M anipulatio Language command. 
  2. When compared with delete TRUNCATE is very fast.
    Step 1:When you perform DELETE all  data in table gets copied first in the  Tablespace with name Rollback.first
  3.  Then delete operation get performed.
  4. Benefit of delete is  after deleting the content of table if you try to RollBack then you restore all your previously deleted data since it is stored in tablespace Rollback .But when you perform TRUNCATE operation,it completely removes all the data directly no extra copy is maintained .Thats why TRUNCATE is faster.Once you Truncate you can’t restore data.
  5. If you consider the TRUNCATE command ,Trigger is not executed .On the other hand if you execute DELETE  Trigger gets executed.
  6. You can never specify WHERE clause with TRUNCATE. DELETE commands comes with flexibility of WHERE claus
  7. You cannot execute rollback after performing TRUNCATE operaton but in DELETE you have freedom to do rollback.TRUNCATE allows you to remove  records permanently.

Leave a Reply

Your email address will not be published. Required fields are marked *