[T-SQL] Delete vs Truncate

Here is good comparision between delete and truncate

DELETE:

• DELETE supports a WHERE clause
• DELETE removes rows from a table, row-by-row
• Because DELETE moves row-by-row, it acquires a row-level lock
• Depending upon the recovery model of the database, DELETE is a fully-logged operation.
• Because DELETE moves row-by-row, it can fire off triggers

TRUNCATE:

• TRUNCATE does not support a WHERE clause
• TRUNCATE works by directly removing the individual data pages of a table
• TRUNCATE directly occupies a table-level lock.
• (Because a lock is acquired, and because TRUNCATE can also participate in a transaction, it has to be a logged operation)
• TRUNCATE is, therefore, a minimally-logged operation; again, this depends upon the recovery model of the database
• Triggers are not fired when TRUNCATE is used (because individual row deletions are not logged)