DELETE

TRUNCATE

DML Statement

DDL Statement

Data Rollback is possible, all the row by row data deletion will be recorded in transaction log

Rollback is possible for entire table and not at row level, since data page reallocation will be logged in transaction log

Eg.)

BEGIN TRAN

TRUNCATE Table Tablename

ROLLBACK

No Commit is performed neither before nor after. (Because it is a DML Statement)

It issues a COMMIT before it acts and another COMMIT afterward so no rollback of the transaction is possible. (Because it is a DDL Statement)

Row level locking will happen while performing deletion

Table level locking will be happen while performing truncation

A delete does not move the High Water Mark of the table back to zero, it retains its original position.

A truncate moves the High Water Mark of the table back to zero

WHERE statement can be included

WHERE statement cannot be included

Activates DML trigger

Doesn’t activate DDL trigger

Able to execute the command on the table that contains foreign key

Not able to execute the command on the table that contains foreign key

Consume more resources since need to process row by row and all these are logged operation

Consume less resource since it will process the full table and its non-logged operation