Difference between TRUNCATE, DELETE and DROP commands - Bug Reaper

                  Bug Reaper

Lean about Automation Testing,Selenium WebDriver,RestAssured,Appium,Jenkins,JAVA,API Automation,TestNG,Maven, Rest API, SOAP API,Linux,Maven,Security Testing,Interview Questions

Monday, 5 May 2014

Difference between TRUNCATE, DELETE and DROP commands

Difference between TRUNCATE, DELETE and DROP commands


Delete is a DML(Data Manipulation Language). Delete command deletes records from the existing table
After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.

Delete is not Autocommit Statement(In fact none of the DML are auto commit)

Drop and Truncate both are DDL(Data Definition Language).

Drop {Delete or drops} the table with it's structure. It is autocommit statement. Drops Once fired can not be rolled back.
DROP rapidly removes all rows and the table's definition.All the tables' rows, indexes and privileges will also be removed

Truncate is the command used to delete all record from table. but the structure of the table remain same.It is also a autocommit statement.
TRUNCATE rapidly removes all rows from a table while maintaining the table definition.

TRUNCATE is much faster than DELETE.

Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed. Thats why when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace. Thats why TRUNCATE is faster.Once you Truncate you can't get back the data.

No comments:

Post a Comment