What are the differences in consequences when using DELETE and TRUNCATE commands to delete data from a table in a database?

Main differences between DELETE and TRUNCATE commands in SQL:

• DELETE:

  • Removes rows from a table based on a condition (if specified), or all rows if no condition is given.
  • The operation is logged row by row, allowing rollback (transactions).
  • DELETE triggers are fired.
  • After deletion, WHERE can be used for selective removal.

• TRUNCATE:

  • Removes all rows from a table completely.
  • Usually faster, as it does not log each row deletion but simply frees data pages.
  • DELETE triggers do not fire.
  • Does not support WHERE condition.
  • Can reset auto-increment counters.

In summary, DELETE is more flexible and safer for selective deletion, while TRUNCATE is a quick way to completely clear a table.