ProAnswers.org

What is the difference between dropping, truncating and deleting from a table

What is the difference between dropping, truncating and deleting from a table.

  1. Dropping - (Table structure + Data are deleted), Invalidates the dependent objects ,Drops the indexes

    1. Truncating - (Data alone deleted), Performs auto commit and is faster than delete

    2. Delete - (Data alone deleted), Auto commit is not performed.

There is further difference between truncating and deleting. Truncating reclaims the space allocated to the table, with just sparing the initial extent for mere existence of the table. Deleting the entire rows from the table does not release this space allocated, neither high water mark (HWM) i.e. the level to which once rows were stored is reset to zero. The truncate does not have a where clause, so must empty the whole table, whereas delete may use a where clause. Most people describe truncating as just deleting all rows plus committing only, but that is seriously a short definition since release of space is an important aspect of truncating which must not be ignored. Visit for cool database fundas at http://crazy4db.blogspot.in