Diff Between Truncate & Delete

Both these commands can be used to remove data from a table. However, there are significant differences between the two. Truncate command is faster because it does not have the resource overhead of logging the deletions in the log. It also acquires less number of locks and the only record of the truncation is the page deallocation. Thus the records removed using this command cannot be restored. Command wise, you cannot specify a where clause for this command. Besides the advantage of being faster (due to minimal logging), another advantage in the case of SQL Server is that it re-sets the IDENTITY value back to the original value and the deallocated pages can be re-used. Besides the limitation of not being able to restore the data, another limitation is that it cannot be used for tables that are involved in replication (or log shipping in the case of SQL Server) and it cannot be used on the tables that are referenced by foreign keys. In addition, this command does not fire the triggers.

Delete command on the other hand logs each and every row in the log. It consumes more database resources and locks. However, the data can be restored easily, you can specify a where clause and the triggers get honored. In SQL Server, you would need to re-seed the identity value using the DBCC CHECKIDENT command once you are done with the delete statement.

TRUNCATE is typically used in data warehousing applications for removal of the data in the staging tables while doing the loads.

In the case of SQL Server, you can rollback a truncate command in SQL Server.