GeekInterview.com
Series: Subject: Topic:
Question: 131 of 133

What is difference between TRUNCATE & DELETE


Answer posted by Scott on 2005-05-25 18:30:04: TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is released back to the server. 
DELETE is a DML command and can be rolled back. 
 
Both commands accomplish identical tasks (removing all data from a table), but TRUNCATE is much faster.
Asked by: Interview Candidate | Asked on: Sep 7th, 2005

Editorial / Best Answer

Answered by: Prangya Sahu

Answered On : Nov 21st, 2005

1>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.

Showing Answers 1 - 38 of 38 Answers
Scott

Answered On : May 25th, 2005

TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is released back to the server. DELETE is a DML command and can be rolled back.  Both commands accomplish identical tasks (removing all data from a table), but TRUNCATE is much faster.

Yes  3 Users have rated as useful.
  
Login to rate this answer.
Deshbir Singh

Answered On : Jun 16th, 2005

TRUNCATE : You can't use WHERE clause DELETE : You can use WHERE clause

Yes  1 User has rated as useful.
  
Login to rate this answer.
rajiv

Answered On : Aug 19th, 2005

Hi some more truncate = delete+commit -so we cant roll back delete = delete- so it can be rolled back

  
Login to rate this answer.
Jagdish

Answered On : Aug 22nd, 2005

Delete - delete deletes the records from table it can be rollbacked also you can give the where condiition to it. Truncate - delete all records from table There is no rollback it always commit without givening the commit 

Yes  1 User has rated as useful.
  
Login to rate this answer.
Luisa G. Reina

Answered On : Sep 7th, 2005

Truncate: Drop all object's statistics and marks like High Water Mark, free extents and leave the object really empty with the first extent. Delete: You can keep object's statistics and all allocated space.

Yes  2 Users have rated as useful.
  
Login to rate this answer.
koteswar rao

Answered On : Oct 14th, 2005

Hiwhat is the difference between truncate and delete

  
Login to rate this answer.
annathurai

Answered On : Oct 15th, 2005

TRUNCATE is a DDL command and cannot be rolled back and All of the memory space is released back to the server. It can not use the Where conditions. DELETE is a DML command and can be rolled back.  here can be use where conditions. TRUNCATE is much faster.

  
Login to rate this answer.
santapan

Answered On : Nov 10th, 2005

View all answers by santapan

Truncate is a Transcation control language & is run under set auto commit true status.Where as delete is a DML operation where we can easily rolled back the query output.

  
Login to rate this answer.
Prangya Sahu

Answered On : Nov 21st, 2005

1>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.

Yes  4 Users have rated as useful.
  
Login to rate this answer.
Deepika S. Verma

Answered On : Dec 27th, 2005

The Main Difference Between DELETE & TRUNCATE Are :-[1] DELETE - is a DML Command & TRUNCATE - is a DDL Command[2] After DELETE - can rollback the Records & After TRUNATE - cannot rollback the records[3] In DELETE Command you can give the conditions in WHERE Clause & In TRUNCATE you cannot give conditions [4] After using DELETE Command The memory will be occupied till the user does not give ROLLBACK or COMMIT & After using TRUNCATE Command The memory realeased immediately

  
Login to rate this answer.
bolluveeranjaneyulu

Answered On : Dec 28th, 2005

when ever u r using delete statement the trigger is fired.in truncated trigger is not fired.we can mention where clause in delete.in truncate we can't mention.

  
Login to rate this answer.
roshan

Answered On : Mar 10th, 2006

  
Login to rate this answer.
Ravi

Answered On : Mar 10th, 2006

You can Rollback Truncate also.

  
Login to rate this answer.
roshan David

Answered On : Mar 11th, 2006

Ravi Rollback is possible only if the rollback command is used before the datapages that were freed due to truncate have not been reused.

  
Login to rate this answer.
shyam

Answered On : Jun 9th, 2006

Hi,   I did not understand what u r telling.could u plz explain the main theme.

  
Login to rate this answer.
pravin maliye

Answered On : Aug 31st, 2006

Hi roshan,How come u know that datapages which are freed by the truncate are being used or not.

  
Login to rate this answer.
vyandy

Answered On : Nov 20th, 2006

execute these commands in SQL Serverbegin transactiondelete from Arollback transactionbegin transactiontruncate table Arollback transactionboth queries give same outputso in case of trucate also we can rollback. please tell me what is the main difference.

  
Login to rate this answer.
Praveen

Answered On : Dec 4th, 2006

DDL command can be rolled back in SQL 2005 .

  
Login to rate this answer.
Mukesh Gupta

Answered On : Dec 6th, 2006

  I don't agree with the statement that Truncate Command cann't be rollbacked. As per my experience both Truncate and Delete Commands can be rollback.

  
Login to rate this answer.
NARENDRA

Answered On : Dec 6th, 2006

TRUNCATE:COMMIT+DELETE;there will be no qs of roll back and where clause.all the records will be deleted only the structure remains.DELETE:it is used to remove the specific records.with the help of the where clause.untill commit is applied the effect of the delete cmd will be temporary on the table.DROP:Both the structure and records will be deleted whenever drop is applied.no qs of roll back.even it is applied  there will be no qs of retrievement of the dropped table.

  
Login to rate this answer.
amol

Answered On : Mar 20th, 2007

Truncate deletes just the page associated with the table so all indexes are resetwhile delete just deletes the specific rows filtered by where statement.and log is maintained for it

  
Login to rate this answer.
riteshratna

Answered On : May 11th, 2007

View all answers by riteshratna

  
Login to rate this answer.
Mahendra

Answered On : May 24th, 2007

Truncate cannot be used if there is child table having a Foreign key relationship. So you have to disable the constraint firstDelete can be used if there is a child table having a foreign key constratint with delete cascadae or on delete set to null clause.

  
Login to rate this answer.
Karthikeyan

Answered On : Jun 12th, 2007

Truncate : Cannot rollback where as delete can be rolled backMain difference is,
if your table is using any identity column, if you truncate the table and again
insert a new value, the identity columns value will start with 1, but If you
delete all the rows from table and insert new values, then the identity column value start with previous value.
i.e., say for example:
My current identity column value is 56, so i have truncate the table, but if i try to insert
a new value, at that time the value of the identity column starts with 1, but if
i delete all the rows from table and then try to insert a new value, at that time the identity column values
will be 57,
this is the main difference and further,
Delete information are logged and captured in log tables but not so in Truncate.

  
Login to rate this answer.
veeresh

Answered On : Aug 6th, 2007

Truncate: You can delete all rows. But you cannot rollback and memory will get released back to serverDelete: You can delete all rows and also rollback.

  
Login to rate this answer.
Gaurav.rsp

Answered On : Dec 22nd, 2007

View all answers by Gaurav.rsp

 Be specific yaar.its the difference in the type of statement

  
Login to rate this answer.

DELETE is a logged operation on a per row basis.  This means that the deletion of each row gets logged and physically deleted. You can DELETE any row that will not violate a constraint, while leaving the foreign key or any other contraint in place. TRUNCATE is also a logged operation, but in a different way.  TRUNCATE logs the deallocation of the data pages in which the data exists.  The deallocation of data pages means that your data rows still actually exist in the data pages, but the extents have been marked as empty for reuse.  This is what makes TRUNCATE a faster operation to perform over DELETE. You cannot TRUNCATE a table that has any foreign key constraints.  You will have to remove the contraints, TRUNCATE the table, and reapply the contraints. TRUNCATE will reset any identity columns to the default seed value.  This means if you have a table with an identity column and you have 264 rows with a seed value of 1, your last record will have the value 264 (assuming you started with value 1) in its identity columns.  After TRUNCATEing your table, when you insert a new record into the empty table, the identity column will have a value of 1.  DELETE will not do this.  In the same scenario, if you DELETEd your rows, when inserting a new row into the empty table, the identity column will have a value of 265.

  
Login to rate this answer.

Truncate :1. DDL comand.2. Auto Commit.3. Cannot delete selected rows.4. Syntax : truncate table ;Delete :1. DML command.2. Not auto commit.3. Selected rows can be deleted.4. Syntax : delete * from where

  
Login to rate this answer.
battu

Answered On : Mar 1st, 2010

View all answers by battu

Major Differences1. TRUNCATE is DDL(Data Defenation Language),DELETE is DML(Data Manipulation Language).2. Both TRUNCATE AND DELETE commands deletes the records from table.3. DELTE command deletes the records with the condition(Ex:-WHERE clause) or row by row deletions. TRUNCATE deleted the al records at a time, before deltion it takes the following steps           a) First creates dummy table(ex:-CREATE TABLE EMP1 AS SELECT * FROM EMP)           b)Now deletes the orginal table( DELETE TABLE EMP)---All rows will be deleted           c)Now Renames the dummy table to actual table (RENAME EMP1 to EMP)4. We can apply the ROLLBACK commind in DELETE, but we can't apply in TRUNCATE5. TRUNCATE is faster than the DELETE command.6. Trigger object doesn't get fired in TRUNCATE, but in DELETE trigger will be fired.

  
Login to rate this answer.
sukha125

Answered On : Mar 18th, 2010

View all answers by sukha125

Create a  table Employee in SQL Server and try the following.. SELECT * FROM EmployeeBegin Transaction Truncate table Employeeselect * from EmployeeRollback Transactionselect * from EmployeeI was able to rollback...with both delete as well as truncate

  
Login to rate this answer.
csbhaskar

Answered On : Mar 29th, 2011

View all answers by csbhaskar

TRUNCATE is a Data Definition Language operation whereas DELETE is a Data Manipulation Language operation.TRUNCATE is much faster than DELETE. (Reason: When executing DELETE statement, it first writes the deleted parts of the table into rollback tablespace. This is why, when we execute a rollback statement, the deleted part is re-written to the table from the rollback tablespace. TRUNCATE statement omits the writing part into the rollback tablespace, and hence faster than the DELETE statement).In Oracle TRUNCATE is implicitly followed by a commit. Therefore, one cannot get the data back after it is removed by TRUNCATE statement. Further, as TRUNCATE is a DDL statement, when it is executed all other pending operations is committed implicitly. But, if a table or some part of a table is removed by a DELETE statement, one can get it back by executing ROLLBACK command, before it is committed implicitly or explicitly.TRUNCATE statement cannot be followed by a WHERE clause, therefore, the user has to TRUNCATE the whole table. One cannot choose to TRUNCATE a part of a table. However, DELETE statement can be followed by a WHERE clause. User has the option to DELETE the whole table or a part of it.If there are referential integrity among tables, TRUNCATE statement should NOT be used. It is not going to check for referential integrity and therefore results in inconsistent data. Therefore, it is recommended to use DELETE statement in such situations.TRUNCATE statement will not fire triggers, but triggers can be written for DELETE statements.TRUNCATE statement resets the High Water Mark for a table, therefore, after executing TRUNCATE operation other table operations becomes much faster. But DELETE does not resets the high water mark.

  
Login to rate this answer.

This is very much easy ansTRUNCATE is a DDL Command and connot be rolled backDELETE   is a  DML Command & can be roll back.TRUNCATE IS MOST FASTER WHEN COMPARED WITH DELETE.............

  
Login to rate this answer.
siddhesh

Answered On : Aug 3rd, 2011

You can rollback truncate statement....Pls check on net...

  
Login to rate this answer.
Nagaraju Byri

Answered On : Sep 16th, 2011

U should agree truncate command execution table space is very less delete is more.

  
Login to rate this answer.
Y.KRISHNA REDDY

Answered On : Feb 3rd, 2012

Using delete command one record or multiple records or all records can be deleted.
Using truncate delete all records from the table.

  
Login to rate this answer.
ch.v.sambasivarao

Answered On : Jul 10th, 2012

TRUNCATE- truncate is a DDl command,it deletes the records permanently AND it cannot delete specific record.

DELETE- delete is DML command,it delete the records temporarily AND get it the record by using command called rollback,it can delete specific record.

  
Login to rate this answer.
saravanan

Answered On : Sep 18th, 2012

Delete & truncate command will delete entries from a table.. where as we can delete a single entry using delete but incase of truncate it wont be possible..

using truncate will delete the log files,buffer storage in a table permanently but incase of delete it wont

  
Login to rate this answer.
Shahida Sultana

Answered On : Mar 6th, 2014

TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesnt use as much undo space as a DELETE.

  
Login to rate this answer.

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.