Need a Translation?

What is the difference between DELETE & TRUNCATE commands in SQL?

DELETE command removes the rows from a table based on the condition that we provide with a WHERE clause. TRUNCATE command actually removes 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 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.
  • TRUNCATE always locks the table and the data page but not each and every row.
  • TRUNCATE cannot be used on a table referenced by a FOREIGN KEY constraint.
  • TRUNCATE cannot be used with a WHERE clause.
  • TRUNCATE cannot activate a trigger because the operation does not log individual row deletions.
  • TRUNCATE can not be Rolled back using logs.
  • TRUNCATE is DDL Command.
  • TRUNCATE Resets identity of the table. The counter used by an identity for new rows is reset to the seed for the column.

SYNTAX: TRUNCATE TABLE #table_name#

DELETE

  • DELETE is slower than TRUNCATE because it maintains log for every record.
  • DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
  • DELETE locks each row in a table for deletion.
  • DELETE can be used with or without a WHERE clause.
  • DELETE activates Triggers.
  • DELETE can be Rolled back using logs.
  • DELETE is DML Command.
  • DELETE does not reset identity of the table.

SYNTAX: DELETE FROM #table_name# WHERE #condition#
 

No comments:

Post a Comment