Welcome Guest. Sign in or Signup

4 Answers

Truncate vs Delete

Asked by: 5371 views SQL

Truncate and Delete are two ways you can use to remove data from a table. But there are many differences between these methods. Can you compare these methods?

Related Questions

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 Thumb up 0 Votes Thumb down 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 Thumb up 0 Votes Thumb down 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 Thumb up 0 Votes Thumb down 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 Thumb up 0 Votes Thumb down 0 Votes

Answer Question