Difference between Delete, Drop and Truncate

Difference between Delete , Drop and Truncate

There is thin line difference between Delete, Drop & Truncate Delete : Delete command is used to remove the rows of the table or data with-in rows, it is used with where clause.
If no where clause is used then all the records will be deleted.
But it does not free the space containing the table.
also this action can be roll back.

Truncate : The SQL TRUNCATE command is used to delete all the rows from the table and all space occupied by the table will become free.
Also this operation can't be rolled backed.
No triggers will be fired.

Drop : Drop command is used to remove the table permanently.
All the rows with stable structure will be removed.
Also when the table is dropped we can not get it back.
No DML trigger will be fired.

Example of Delete, drop & truncate :

Example: Consider below table : Salary

Emp_id	Emp_salary	Emp_desig
01	25000	Software Eng
02	30000	Senior Software Eng
03	25000	Software Analyst

Now it is required to delete a row from Table then

Query of DELETE:

Delete
from Salary
Where Emp_id = 01
Result:

Emp_id	Emp_salary	Emp_heading
02	30000	Senior Software Eng
03	25000	Software Analyst

Example of delete, without where clause
Delete
from Salary

Result: If where clause is not used then all the records will be deleted.

Emp_id	Emp_salary	Emp_heading

Tag : What is Delete, Drop & Truncate in SQL?, difference between drop & delete