Difference Between TRUNCATE, DELETE and DROP commands

1.DELETE

• The DELETE command is used to remove rows from a table. 
• A WHERE clause can be used to  remove some rows. 
• If no WHERE condition is specified, all rows will be removed. 
• After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.
• Note that this operation will cause all DELETE triggers on the table to fire.

 

2.TRUNCATE

• TRUNCATE removes all rows from a table. 
• No triggers will be fired. 
• Not able to perform ROLLBACK operation.
• TRUNCATE is faster then DELETE and doesn't use as much undo space as a DELETE.

3.DROP

• The DROP command removes a table from the database.
•  All the tables' rows, indexes and privileges will also be removed. 
• No DML triggers will be fired. 
• Not able to perform ROLLBACK operation.

SOME MORE DIFFERENCES

• DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. 
• DELETE operations can be ROLLBACK (undone), while DROP and TRUNCATE operations cannot be ROLLBACK.

 

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!