It is a common misconception that truncating data in SQL Server cannot be undone. However, this is not the case. In fact, truncating a table is a fully registered operation that can be reversed with a rollback. This is in contrast to Oracle, where truncating cannot be undone.
To understand why this is the case, it is important to understand the difference between delete and truncate. The TRUNCATE command deallocates pages by marking them as free on the GAM and IAM pages and records the deallocation information in the transaction log. This means that while the data still exists on the data pages, it has been marked as empty for reuse. TRUNCATE TABLE requires fewer system and transaction logging resources than an equivalent DELETE command.
However, it does not record the deletion of records, but rather records the deallocation of pages. As such, TRUNCATE TABLE is a registered command and can be undone with a huge performance advantage over an equivalent DELETE. Since TRUNCATE is a DDL (data definition language) statement, it cannot be undone if it is not inside a transaction or if the transaction is COMMITTED. To verify this, one can create one table as a copy of another (including content), let it show which pages this table uses, start a transaction, run a TRUNCATE, and repopulate the table. In order to recover records deleted or truncated, one must have saved the same data in the log. SQL Server uses the transaction log for any rollback operation, so when it comes to TRUNCATE, you will still be able to reverse the transaction since TRUNCATE operations are also logged.
Leave Reply