August 03, 2018

Delete, Drop and Truncate

What is the difference between Delete and Truncate commands.
Delete and truncate both commands can be used to delete data of the table.

e.g:
//Truncate Query
TRUNCATE TABLE table_name;

//Delete Query
DELETE FROM table_name WHERE < condition > ;

Difference
  • Delete is a DML(Data Manipulation Language) command and is used when we specify the row(tuple) that we want to remove or delete from the table or relation. Where as Truncate is a DDL(Data Definition Language) command and is used to delete all the rows or tuples from a table.  In short Delete is used to delete specific data and Truncate is used to delete the entire data of the table. 
  • We can use with where clause with Delete, but we cannot use where clause with Truncate.
  • Delete locks the table row before deleting the row. The Truncate locks the entire table. 
  • We can rollback the changes after executing Delete command, however we can’t rollback the changes after Truncate.
  • Delete is slower than truncate 
Explain Drop command.
Drop is a Data Definition Language Command (DDL), which is used to drop the whole table. With the help of this command we can drop the whole structure in one go.

-K Himaanshu Shuklaa..

No comments:

Post a Comment