Truncate vs Delete in MSSQL .

Delete in MSSQL

• 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 in MSSQL

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