DELETE Comand Vs. TRUNCATE Command

TRUNCATE command :

  • TRUNCATE command is faster in compare to  DELETE command as it uses fewer system & the transaction log resources .
  • This commands removes all the data by deallaocating the data pages which are used to store the table’s data & only page deallocations are recorded/ stored  in the transaction log.
  • This command will remove all the rows from  table but having table structures ,its  constraints,  columns,  indexes and so on … remains. The columns used by an identity for new rows is reset to the seed for the column.
  • You can not use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint because table i.e.TRUNCATE is not logged, as it can’t activate the trigger.
  • This command can’t be rolled back.
  • TRUNCATE is  DDL type of command.
  • This command resets identity of the table.
  • WHERE clause can’t be used with TRUNCATE command .

DELETE Command :

  • DELETE Command removes rows on at a time and records and 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.