Truncate vs Delete

4 Answers

1. 
   

   
   

   Janardhan on Mar 02, 2013 Reply

   The main difference between the both is,
   We can use rollback, if we used truncate but we can’t rollback if we used delete.

   -8 Votes 0 Votes  0 Votes

2. 
   

   
   

   mpankaj on Apr 17, 2013 Reply

   It depends where you are using….If using in AE , %TruncateTable means delete + Commit. So you cant rollback if you did %Truncatetable in AE.
   If using in SQL, truncate can delete all rows i.e. we cant put where condition in truncate statement where in delete sql we can.

   0 Votes 0 Votes  0 Votes

3. 
   

   
   

   Jyothi on Jul 16, 2013 Reply

   Janardhan has told in reverse way..Actually we can rollback for delete statement and can’t rollback for truncate..Please explain if I am wrong

   +7 Votes 0 Votes  0 Votes

4. Best Answer
   
   

   
   

   Steve Bollinger on May 21, 2015 Reply

   Truncate is a DDL command which automatically incorporates an automatic commit, even inside a transaction. Truncate does not actually delete rows.
   It moves the highwater mark to the top so all rows are inaccessible effectively gone. no way to recover or rollback from a truncate under any conditions because no rows were deleted thus not recorded in the DB Logs.
   Delete removes individual rows and are recorded in the DB logs.
   Thus a rollback before commit will recover them.

   +3 Votes 0 Votes  0 Votes