Monday, 27 November 2017

Top 7 differences between DELETE, DROP and TRUNCATE in SQL

Difference between Delete, Drop and Truncate is one of the most confusing concept for most of the people. However, this is the basic concept and easy to remember if understood correctly. Please find the differences with it's respective benefits while working for daily purpose as below:

  1. DELETE operates on row-by-row whereas DROP and TRUNCATE works on table level.
  2. Triggers get fired after execution of DELETE command whereas no triggers get fired in DROP and TRUNCATE.
  3. DELETE and TRUNCATE can be COMMITTED and ROLL-BACKED if provided inside a transaction whereas DROP can’t be COMMITTED OR ROLL-BACKED.
  4. TRUNCATE execute faster than DELETE as it works at table level whereas DELETE removes records row-by-row one at a time and an entry get recorded in the Transaction logs.
  5. DELETE does not free the space containing the table whereas DROP and TRUNCATE free the space containing the table.
  6. WHERE clause can be used in DELETE command whereas in DROP AND TRUNCATE can’t use it.
  7. DELETE command keeps the lock over each row whereas TRUNCATE keeps the lock on table not on the entire row and DROP doesn’t keep lock neither at row nor table.

No comments:

Post a Comment