Let us understand the difference between delete truncate and drop table:
Delete Truncate and Drop Table:
Characteristics | Delete | Truncate | Drop |
Command Type | DML(Data Manipulation Language) | DDL(Data Definition Language) | DDL(Data Definition Language) |
Rollback transaction | Can be rolled back | Cannot be rolled back | Cannot be rolled back |
Permanent delete | Since it is DML does not remove records permanently | Since it is DDL removes the records permanently | Since it is DDL removes the records permanently |
Trigger | Trigger is fired | No trigger used | No trigger used |
Performance | Slower than truncate | Faster than delete | Quick but some complications |
Can we use WHERE clause? | Yes WHERE clause can be used | No WHERE clause can be used | No WHERE clause can be used |
Syntax | DELETE FROM table_name; | TRUNCATE table_name; | DROP table_name; |
Usage | Used to delete records in the table | Used to delete data and keep the table schema as it is | Used to delete data as well as table structure. |
Example: | Delete from Employee where salary>25000; | Truncate table Employee; | Drop table Employee; |
With the above mentioned tabular form difference we can easily understand the difference between delete, truncate and drop in SQL