Start networking and exchanging professional insights

Register now or log in to join your professional community.

Follow

What is the difference between DELETE and TRUNCATE commands?

user-image
Question added by Muhammad Shahid Farooq , Software Engineer, System Integrator , ForeVision Business Solutions
Date Posted: 2017/03/06
Akhil Parvatkar
by Akhil Parvatkar , Senior Software Engineer , Citigroup Global Services Pvt. Ltd.

TRUNCATE is a DDL command whereas DELETE is a DML command.

2>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.Thatswhy 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.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.

3>You cann't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently.

4>In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired.

5>You cann't use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write conditions using WHERE clause

Anjum Rizwi
by Anjum Rizwi , Senior Technical Leader , Valtech India

DELETE is DML command, when we run this query all affected recorded in log. Since it is recorded we can rollback. 

 

Truncate is DDL command. when we run this command it de-allocates the memory that is allocated to this table. System can't record the activity in log. So roll back is not possible. If you have foreign key reference you can't run truncate because system can't check the dependency. 

Muhammad Waqar Arif
by Muhammad Waqar Arif , Senior Database Architect , FINCA Microfinance Bank Limited

Delete is DML command and it can generate on delete entry and record write in log file so that will help to recover.

 

Truncate is DDL command, It removes all rows from table instead of specific one with Where clause like we use in Delete.It is faster command and didn't use space as much as Delete.

More Questions Like This

Do you need help in adding the right keywords to your CV? Let our CV writing experts help you.