Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

 

TRUNCATE

  • TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.

 

  • TRUNCATE removes the data by de-allocating the data pages used to store the table’s data, and only the page de-allocations 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.

 

  • You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.

 

  • Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

 

  • TRUNCATE can not be Rolled back.

 

  • TRUNCATE is DDL Command.

 

  • TRUNCATE Resets identity of the table.

 

DELETE

  • 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. If you want to remove table definition and its data, use the DROP TABLE statement.
  • DELETE Can be used with or without a WHERE clause
  • DELETE Activates Triggers.
  • DELETE Can be Rolled back.
  • DELETE is DML Command.
  • DELETE does not reset identity of the table

Leave a Reply

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

Name *