What is Difference between truncate delete and drop

DELETE
  • DELETE command is used to remove rows from table, it can be used with WHERE clause
  • After DELETE operation you need to COMMIT or ROLLBACK to make the changes permanent
  • DELETE command will cause all DELETE triggers on the table to fire
  • DELETE is DML command
TRUNCATE
  • TRUNCATE removes all rows from tabl,it do not accept WHERE clause
  • The operation cannot be ROLLBACKED and no trigger will be fired
  • TRUNCATE is faster and it does not use much undo space as compared to DELETE
  • TRUNCATE is DDL command
DROP
  • DROP command removes a table from the database. All the table's rows, indexes and privileges will also be removed
  • No DML triggers will be fired. The operation cannot be rolled back
  • DROP is DDL command


      DELETE FROM employee WHERE job = 'CLERK';

      TRUNCATE TABLE employee;

      DROP TABLE employee;




No comments:

Post a Comment