Tuesday, November 20, 2012

Truncate and Delete

Generally Delete command removes the rows from a table based on some where condition. whereas Truncate removes all the rows from a table.


  • Truncate is faster and uses fewer system and transaction log resources than Delete. 
  • You cannot use  Truncate Table on a table referenced by a FOREIGN KEY constraint.
  • Truncate is a DDL Command.
  • Truncate resets the identity of the table.
  • Truncate removes all the rows from a table, but the table structure, its columns, constraints, indexes and so on remains. The counter used by an identity for new rows is reset to the seed for the column.
  • Truncate cannot be rolled back unless it is used in TRANSACTION. OR TRUNCATE can be rolled back when used with BEGIN … END TRANSACTION using T-SQL.
  • 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.
  • Delete removes rows one at a time and records an entry in the transaction log for each deleted row.
  • Delete does not reset the identity of the table.
  • Delete is DML Command.
  • Delete can be rolled back.
  • Delete does not reset Identity property of the table.
  • Delete can be used with or without a WHERE clause.
  • Delete activates Triggers if defined on table.

No comments:

Post a Comment