Systems Engineering and RDBMS

Truncate vs Delete

Posted by decipherinfosys on March 12, 2007

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 the case of 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.  It is also used in maintenance scripts by the DBAs/Developers.  In actual application logic, delete is the right choice because of the limitations that have been mentioned above for the truncate command.

Point of Caution: The behavior of truncate is different between Oracle and SQL Server.  In the case of Oracle, it is a DDL command and hence does an implicit commit which means that you cannot rollback truncate in the case of Oracle.  In the case of SQL Server, that is not the case and you can rollback a truncate command in SQL Server.

One Response to “Truncate vs Delete”

  1. […] had covered the differences between Truncate and Delete commands before – you can read more on that here.   Let’s just try this out rather than making […]

Sorry, the comment form is closed at this time.

%d bloggers like this: