SQL Server - Difference between Delete and Truncate RollBack Transaction.

Asked By aman on 09-Nov-11 01:32 AM
hi all,

i am trying to rollback data  using RollBack Transaction but not getting it.....

please help me with some example...

how to obtain data once we have deleted data using Delete Statement

or it is possible to obtain data once we have used Truncate Statement to delete  the data.

please help with some good example.


Thanks,
Jitendra Faye replied to aman on 09-Nov-11 01:34 AM

Differences-

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

Jitendra Faye replied to aman on 09-Nov-11 01:35 AM

Truncate an Delete both are used to delete data from the table. These both command will only delete data of the specified table, they cannot remove the whole table data structure.Both statements delete the data from the table not the structure of the table.

  • TRUNCATE is a DDL (data definition language) command whereas DELETE is a DML (data manipulation language) command.

  • You can use WHERE clause(conditions) with DELETE but you can't use WHERE clause with TRUNCATE .

  • You cann't rollback data in TRUNCATE but in DELETE you can rollback data.TRUNCATE removes(delete) the record permanently.

  • A trigger doesn’t get fired in case of TRUNCATE whereas Triggers get fired in DELETE command.

  • If tables which are referenced by one or more FOREIGN KEY constraints then TRUNCATE will not work.

  • TRUNCATE resets the Identity counter if there is any identity column present in the table where delete not resets the identity counter.

  • Delete and Truncate both are logged operation.But DELETE is a logged operation on a per row basis and TRUNCATE logs the deallocation of the data pages in which the data exists.

  • TRUNCATE is faster than DELETE.
dipa ahuja replied to aman on 09-Nov-11 02:28 AM

Truncate an Delete both are used to delete data from the table. These both command will only delete data of the specified table, they cannot remove the whole table data structure.Both statements delete the data from the table not the structure of the table.

  • A trigger doesn’t get fired in case of TRUNCATE whereas Triggers get fired in DELETE command. 
  • If tables which are referenced by one or more FOREIGN KEY constraints then TRUNCATE will not work. 
  • TRUNCATE resets the Identity counter if there is any identity column present in the table where delete not resets the identity counter. 
  • Delete and Truncate both are logged operation.But DELETE is a logged operation on a per row basis and TRUNCATE logs the deallocation of the data pages in which the data exists. 

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 
In simple words, after deleting the records you can able to rollback but in case of Truncate once you delete the records you will not be able to rollack what you had deleted earlier.

Anoop S replied to aman on 09-Nov-11 04:16 AM
simple words difference between Delete and Truncate

DELETE
DeLete Is the command that only remove the data from the table. It is DML statement. Deleted data can be rollback. By using this we can delete whole data from the table(if use without where clause).If wE want to remove only selected data then we should specify condition in the where clause.

TRUNCATE
This is the DML command. This command delete the data from table. But there is one difference from ordinary delete command. Truncate command drop the storage held by this table. Drop storage can be use by this table again or some other table. This is the faster command because it directly drop the storage.

You can try this to recover data -> Recovering from a deleted log file on SQL Server
http://www.spaceprogram.com/knowledge/sqlserver_recover_from_deleted_log.html