Monday, December 27, 2010

Difference between Delete, Truncate and Drop

Delete table -- will delete the rows from the table for that session/schema only.
Need to use explicit 'COMMIT' to reflect the changes across the schemas/sessions.

Truncate table -- will delete the rows from the table and an implicit 'COMMIT' command will be executed. Changes will be reflected across the schemas/sessions.


Delete table-- u can use ROLLBACK command to retrieve the deleted records
truncate table -- ROLLBACK can't be used

Delete table -- DML(Data Manipulation Language) Command 
Truncate table-- DDL (Data Definition Language) Command

Delete -- is row by row operation
Truncate -- will delete all the data from table
When ever we have to delete all the data in a table Truncate is suggested over Delete.

Drop will delete table structure also, where as Delete and Truncate will not delete Table structure.

No comments:

Post a Comment