Scott

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

Deshbir Singh

• Jun 16th, 2005

 

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

rajiv

• Aug 19th, 2005

 

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

Jagdish

• 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 

Luisa G. Reina

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

koteswar rao

• Oct 14th, 2005

 

Hiwhat is the difference between truncate and delete

annathurai

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

santapan

 Profile Answers by santapan 

• Nov 10th, 2005

 

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.

Prangya Sahu

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

Deepika S. Verma

• 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

bolluveeranjaneyulu

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

roshan

• Mar 10th, 2006

 

TRUNCATE	DELETE
faster and uses fewer system and transaction log resources than DELETE.
removes the data by deallocating the data pages used to store the table?s data, and only the page deallocations are recorded in the transaction log.	removes  rows one at a time and records an entry in the transaction log for each deleted row.
removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.	If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint;	use DELETE statement without a WHERE clause
Because TRUNCATE TABLE is not logged, it cannot activate a trigger.	Activates Trigger
No Roll back	Can Rollback
DDL Command	DML Command

Ravi

• Mar 10th, 2006

 

You can Rollback Truncate also.

roshan David

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

shyam

• Jun 9th, 2006

 

Hi,

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

pravin maliye

• Aug 31st, 2006

 

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

vyandy

• Nov 20th, 2006

 

execute these commands in SQL Server

begin transaction

delete from A

rollback transaction

begin transaction

truncate table A

rollback transaction

both queries give same output

so in case of trucate also we can rollback. please tell me what is the main difference.

Praveen

• Dec 4th, 2006

 

DDL command can be rolled back in SQL 2005 .

Mukesh Gupta

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

NARENDRA

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

amol

• Mar 20th, 2007

 

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

riteshratna

 Profile Answers by riteshratna 

• May 11th, 2007

 

Delete

This is a DML Statement

Rollback possible

No Commit is performed neither before nor after. (Because it is a DML Statement).

They take locks on rows,


They generate redo (lots of it)


They require segments in the UNDO tablespace.

A delete does not relinquish segment space thus a table in which all records have been deleted retains all of its original blocks.

A truncate does not move the High Water Mark of the table back to zero, it retains it?s original position.

 

Truncate

This is a DDL Statement

Rollback not possible

It issues a COMMIT before it acts and another COMMIT afterward so no rollback of the transaction is possible. (Because it is a DDL Statement)

No row-level locks are taken.


No redo or rollback is generated.

They do not require segments in the UNDO tablespace.

All extents bar the initial are de-allocated from the table

A truncate moves the High Water Mark of the table back to zero

Mahendra

• May 24th, 2007

 

Truncate cannot be used if there is child table having a Foreign key relationship. So you have to disable the constraint first

Delete can be used if there is a child table having a foreign key constratint with delete cascadae or on delete set to null clause.

Karthikeyan

• Jun 12th, 2007

 

Truncate : Cannot rollback where as delete can be rolled back

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

veeresh

• Aug 6th, 2007

 

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

Delete: You can delete all rows and also rollback.

Gaurav.rsp

 Profile Answers by Gaurav.rsp 

• Dec 22nd, 2007

 

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

rajvelur

 Profile Answers by rajvelur Questions by rajvelur

• Jun 5th, 2008

 

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.

nirmal1in

 Profile Answers by nirmal1in Questions by nirmal1in

• Jan 3rd, 2010

 

Truncate :
1. DDL comand.
2. Auto Commit.
3. Cannot delete selected rows.
4. Syntax : truncate table <Table_Name>;

Delete :
1. DML command.
2. Not auto commit.
3. Selected rows can be deleted.
4. Syntax : delete * from <table_name> where <filter conditions>

battu

 Profile Answers by battu 

• Mar 1st, 2010

 

Major Differences

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

5. TRUNCATE is faster than the DELETE command.

6. Trigger object doesn't get fired in TRUNCATE, but in DELETE trigger will be fired.

sukha125

 Profile Answers by sukha125 

• Mar 18th, 2010

 

Create a  table Employee in SQL Server and try the following..

 

SELECT * FROM Employee

Begin Transaction 

Truncate table Employee

select * from Employee

Rollback Transaction

select * from Employee

I was able to rollback...with both delete as well as truncate

csbhaskar

 Profile Answers by csbhaskar 

• Mar 29th, 2011

 

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

katam.srinivasarao

 Profile Answers by katam.srinivasarao 

• Mar 29th, 2011

 

this is very much easy ans

TRUNCATE is a DDL Command and connot be rolled back

DELETE   is a  DML Command & can be roll back.

TRUNCATE IS MOST FASTER WHEN COMPARED WITH DELETE.............

siddhesh

• Aug 3rd, 2011

 

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

Nagaraju Byri

• Sep 16th, 2011

 

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

Y.KRISHNA REDDY

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

ch.v.sambasivarao

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

saravanan

• 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

Shahida Sultana

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