What is difference between DELETE & TRUNCATE commands?

What is difference between DELETE & TRUNCATE commands?

This is most common interview question related to SQL and Database, every programmer must know differnce between Delete and Truncate.

Delete command removes the rows from a table based on the condition that we can write WHERE clause in delete command.

Truncate command will actually remove all the rows from a table, we can not use where clause in traincate command, there is no log maintains for this.

Blow are some differnce between Delete and Truncate.
TRUNCATE

  • TRUNCATE Resets identity of the table.
  • TRUNCATE is faster then Delete beacuse Truncate uses fewer system and transaction log resources than DELETE.
  • TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.
  • TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.
  • You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
  • It can not activate any trigger, because TRUNCATE TABLE is not logged.
  • TRUNCATE can not be Rolled back using logs.
  • TRUNCATE is DDL Command.

DELETE

  • DELETE is DML Command
  • DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
  • If you want to retain the identity counter, use DELETE instead.
  • DELETE Can be used with or without a WHERE clause
  • DELETE Activates Triggers.
  • DELETE Can be Rolled back using logs.
  • DELETE does not reset identity of the table.

Leave a Reply

Your email address will not be published. Required fields are marked *

4 + 1 =


All Rights Reserved 2019 | DesignzRush (OPC) Pvt. Ltd.
Inline
Inline