Truncate vs Delete with MSSQL

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Delete

  • Deleting is one the most expensive operation, it's logged. Every row deleted is logged to the transaction log.
  • Delete from ... allows to you delete specific rows.
  • Delete from ... marks the row as a ghost row.
  • Delete from ... does not release the space of the deleted rows, you will need to run DBCC SHRINKFILE to recover the space [single user mode].

Truncate

  • Often people want to empty a temporary table to repopulate it with current data, then it's much faster to use the truncate statement instead of the delete statement.
  • truncate table is the most efficient way of emptying a table.
  • It's not possible to use the truncate table when you only want to delete some rows based on a condition.
  • Truncate table does not delete rows, it deallocates the data pages and release the space.
  • Truncate table cannot be used when the table is referenced by a foreign key or involved in data replication.
  • Truncate table is recommended for temporary tables that need to be cleared for the next process.