What is difference between TRUNCATE & DELETE ?

TRUNCATE commits after deleting entire table i.e., can not be rolled back. Database triggers do not fire on TRUNCATEDELETE allows the filtered deletion. Deleted records can be rolled back or committed.Database triggers fire on DELETE.
  • Interview Candidate
  • 25
  • 3790

Showing Answers 1 - 25 of 25 Answers

rajaramian

  • Feb 8th, 2006
 

We can ROLLBACK the DELETE statement but we can not ROLLBACK the TRUNCATE statement

  Was this answer useful?  Yes

prince maruthi

  • Feb 9th, 2007
 

truncate=DELETE+COMMIT;
delete=delete+rollback;we can rollback after the delete command ,then we can get the deleted data

  Was this answer useful?  Yes

riteshratna

  • May 11th, 2007
 

Delete
Delete remove the Data only, the Table structure remains intact.

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.
Delete deletes the specific rows filtered by where statement. and log is maintained for it.
It can activate the triggers.

Truncate
Truncate remove the Data only, the Table structure remains intact.
This is a DDL Statement
Rollback not possible (Except in SQL 2005)
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
Truncate deletes the page associated with the table so all indexes are reset
It does not activate the triggers.

  Was this answer useful?  Yes

sheel

  • May 17th, 2007
 

Hi friends
In truncate command deletion of row happen but one important thing is that, after truncate memory space is also empty or you can say that operation going on memory element is deleted from memory permanetly. that is why they cannot rollback.
But in case of delete command the element is still present in memory so memory is not empty after the delete operation so they can rollback after deletion. thanx

if any one has new logic plz send me

  Was this answer useful?  Yes

harneet2004us

  • Aug 24th, 2007
 

1) Rollback : Not possible in Truncate but possible in Delete because it
Delete uses the rollback Segments.

2) Trigger : If we have a Delete Trigger, it will not be fired in case of
Truncate.

3) Type : Delete is a DML Type of statement but Truncate is a DDL statement.

4) Speed : Truncate is much faster than Delete because it does not use the
rollback segments

5) High Water Mark : The High water mark of the table is set to 0 in case of
Truncate but this variable retains its original value in Delete.

  Was this answer useful?  Yes

nandk.sharma

  • Jun 27th, 2008
 

TRUNCATE -  Truncate is a ddl command.

DELETE-     Delete is a dml command.

TRUNCATE - We can not use 'where' clause with truncate.

DELETE-      We can use 'where '  clause  with delete .

  Was this answer useful?  Yes

Give your answer:

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

 

Related Answered Questions

 

Related Open Questions