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.
RE: What is difference between TRUNCATE & DELETE ?
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.
RE: What is difference between TRUNCATE & DELETE ?
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
