MainMenu

Home Core Java Overview Maven Tutorials

Sunday, 15 January 2017

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_idEmp_salaryEmp_desig
0125000Software Eng
0230000Senior Software Eng
0325000Software Analyst

Now it is required to delete a row from Table then

Query of DELETE:


Delete
from Salary
Where Emp_id = 01
Result:
Emp_idEmp_salaryEmp_heading
0230000Senior Software Eng
0325000Software 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_idEmp_salaryEmp_heading


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

No comments:

Post a Comment