Have you ever wondered about the difference between the DELETE and TRUNCATE commands in SQL Server? You may have heard conflicting statements about whether DELETE can be rolled back while TRUNCATE cannot. Let’s clear up any confusion and explore these concepts in simple terms.

When a database is in full recovery mode, the DELETE command can be rolled back using log files. This means that any changes made by the DELETE command can be undone. On the other hand, the TRUNCATE command cannot be rolled back using log files in full recovery mode.

Both DELETE and TRUNCATE commands can be rolled back if they are executed within a transaction and the session is not closed. However, if a TRUNCATE command is executed within a transaction and the session is closed, it cannot be rolled back. In contrast, a DELETE command can still be rolled back even if the session is closed.

Let’s dive a bit deeper into how these commands work. When you use the DELETE command, SQL Server removes all the rows from the table and records the changes in the log file for potential rollback in the future. This process makes the DELETE command slower compared to TRUNCATE.

On the other hand, when you use the TRUNCATE command, SQL Server deallocates the data files in the table and records the deallocation in the log files. If the deallocated data files are overwritten by other data, it can be recovered using rollback. However, there is no guarantee of rollback in the case of TRUNCATE.

Let’s see an example of how TRUNCATE can be rolled back for a particular session using T-SQL:

BEGIN TRAN
TRUNCATE TABLE TestTable

-- The following SELECT statement will return an empty TestTable
SELECT * FROM TestTable

-- The following SELECT statement will return TestTable with the original data
ROLLBACK
SELECT * FROM TestTable

Summary: In SQL Server, the DELETE command can always be recovered from the log file if the database is in full recovery mode. However, the TRUNCATE command may or may not be recoverable from log files. It’s important to understand the implications of these commands and use them accordingly based on your specific requirements.