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

• 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

• 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.