There are two main statements used for deleting data from a table in SQL SERVER:
The two commands achieve the same result; however, each of the commands acts a little bit different. Each of the two commands have advantages, limitations, and consequences, which have to be considered when deciding which method to use.
In this post we will concentrate in one aspect of these commands usage – the ability to rollback each operation.
DELETE statements delete rows one at a time, logging each row in the transaction log and maintaining the log sequence number (LSN) information.
Even though it will consumes more database resources, it comes in handy as these transactions can be rolled back if necessary by using the log files, when the recovery model of the database is set to full.
But what happens when you issue the TRUNCATE command?
The TRUNCATE statement is much faster statement than the DELETE command.
It deletes all records in a table by deallocating the data pages used to store the table's data. This operation has a limited logging in the transaction log (only the page deallocations are recorded in the transaction log). In addition fewer locks are acquired with this statement in compare to the DELETE statement.
As a result of the above it is commonly believed that records removed by the TRUNCATE statement cannot be restored – rolled back.
It is true in most cases, however DELETE and TRUNCATE can both be rolled back when they are executed inside a 'BEGIN TRANSACTION' block and the current session has not yet ended.
In other words, when the TRUNCATE statement has not been committed yet, it can be rolled back
The following code demonstrates a scenario when a TRUNCATE can be rolled back for that particular session.
| || |
DELETE can always be rolled back-
- When the recovery model of the database is set to SIMPLE, the statement can be rolled back – when the statement is executed inside a 'BEGIN TRANSACTION' block.
- When the recovery model of the database is set to FULL, the statement can also be recovered from the log files.
TRUNCATE can or cannot be rolled back, depends if it is executed from within a 'BEGIN TRANSACTION' block.