Pages

Sunday, July 29, 2012

Difference between DELETE and TRUNCATE command

DELETE and TRUNCATE in SQL:

DELETE:

  • Delete is a DML(Data manipulation language) command.
  • DELETE statement only locks a row in table while performing DELETE operation.
  • We can use WHERE clause. So that it deletes specified data by filtering with WHERE clause.
  • DELETE activates a trigger. Because operation logs individually.
  • Slower than TRUNCATE. because it keeps logs.
  • Since DELETE keeps logs, Rollback is possible.
TRUNCATE:
  • TRUNCATE is a DDL(Data Definition Language) command.
  • TRUNCATE statement locks table and page, but not each row.
  • Cannot use WHERE clause.
  • No trigger activates. Since it doesn't log individually.
  • Faster than DELETE in performance wise. Because, it doesn't keeps logs.
  • Since it doesn't keeps logs, Rollback is not possible.


No comments:

Post a Comment