ابدأ بالتواصل مع الأشخاص وتبادل معارفك المهنية

أنشئ حسابًا أو سجّل الدخول للانضمام إلى مجتمعك المهني.

متابعة

What is the difference between DELETE and TRUNCATE commands?

user-image
تم إضافة السؤال من قبل Muhammad Shahid Farooq , Software Engineer, System Integrator , ForeVision Business Solutions
تاريخ النشر: 2017/03/06
Akhil Parvatkar
من قبل 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
من قبل 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
من قبل 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.

المزيد من الأسئلة المماثلة

هل تحتاج لمساعدة في كتابة سيرة ذاتية تحتوي على الكلمات الدلالية التي يبحث عنها أصحاب العمل؟