Truncate Vs Delete...Can Truncate be Rolled back in SQL Server?

Lets begin with some straight QUESTIONS???

We have with us...A TestTable with TestID Column.

Use this code to prepare this set up:

--Create a TestTable with TestID Column
CREATE TABLE TestTable (TestID INT)

--Insert some records in this Table
INSERT INTO TestTable Values(101),(102),(103),(104),(105)

--Run select to crosscheck 
SELECT * FROM TestTable
GO

We have TestTable with following records now:

TestID
101
102
103
104
105

Now...What will be the output of these three T-Sql Statements when run independently ?

Scenario 1:

-- Begin The Transaction
BEGIN TRAN
--Delete statement
DELETE from TestTable
-- Rollback Transaction
ROLLBACK TRAN
GO
-- Check output after Delete
SELECT * FROM TestTable
GO

Scenario 2: 

BEGIN TRAN
--Truncate statement
TRUNCATE Table TestTable
-- Rollback Transaction
ROLLBACK TRAN
GO
-- Check output after Truncate
SELECT * FROM TestTable
GO

Scenario 3:

BEGIN TRAN
--DROP statement
DROP Table TestTable
-- Rollback Transaction
ROLLBACK TRAN
GO
-- Check output after DROP
SELECT * FROM TestTable
GO

Now let me put some doubts in your mind before you answer...

1. Truncate and Drop are DDL statement. Are they logged - Yes or No? What is their logging behavior. Is this possible to Rollback in case of Truncate?

Now Run your queries in all these 3 scenarios to clear your doubts...

ANSWER:(All 3 scenarios when run independently i.e. not one after the other) gives same output i.e. In all 3 scenarios Transactions have been rolled back giving the following output:

 TestID
101
102
103
104
105

Scenario 4: What output will you get in this case?

BEGIN TRAN
--Truncate statement
Truncate Table TestTable
--DROP statement
DROP Table TestTable
-- Rollback Transaction
ROLLBACK TRAN
GO
-- Check output after Truncate and Drop
SELECT * FROM TestTable
GO

Answer : In this case also when both Truncate and Drop statements are there inside the BEGIN TRAN and ROLLBACK TRAN we get the same output i.e. Rollback has been done and output which we get is :

 TestID
101
102
103
104
105

Scenario 5: What output will you get in this case. When we specify drop before Truncate within BEGIN TRAN and ROLLBACK TRAN ?

-- Begin The Transaction
BEGIN TRAN
--DROP statement
DROP Table TestTable
--Truncate statement
Truncate Table TestTable
-- Rollback Transaction
ROLLBACK TRAN
GO
-- Check output after Truncate and Drop
SELECT * FROM TestTable
GO

Answer: In this case you will get This Message in the Messages Tab along with This output in Results Tab since the drop is used before Truncate within Transaction so finally whole Transaction is rolled back and the select statement outside the Transaction still comes up with 5 records.

Messages Tab:

Msg 4701, Level 16, State 1, Line 6
Cannot find the object "TestTable" because it does not exist or you do not have permissions.

Results Tab:

TestID
101
102
103
104
105

Having run all these queries Lets Draw out some conclusions and get our basics correct:

1. Within a Transaction TRUNCATE can be rolled back. As mentioned in Point No 4 below; Truncate also does logging but the nature of logging as compared to Delete is different. This is why in our examples above we were able to Roll back in case of Truncate.

2. Truncate can't be rolled back if no transaction is used. Truncate operation when you run outside a Transaction is auto committed so You will not be able to rollback if you use Truncate outside a Transaction. i.e. Once committed it is not possible to rollback Truncate and DROP.

3. TRUNCATE removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; However, TRUNCATE TABLE is faster because it uses fewer system & transaction log resources.

4. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. We can say A DELETE statement is always fully logged. TRUNCATE TABLE removes the data by de-allocating the data pages used to store the table data and records only the page de-allocations in the transaction log. Hence less Transaction Log space is used in case of TRUNCATE.

5. Truncate uses Fewer locks. When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. On the contrary TRUNCATE TABLE do not lock each row as Delete does but always locks the table (including a schema (SCH-M) lock) and page.

6. TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. We have to use DROP TABLE to remove the table definitions well as its data.

7.  TRUNCATE TABLE cannot activate a trigger because the operation does not log row - by - row deletions.

8. DROP removes one or more table definitions and all data, indexes, triggers, constraints and permission specifications for those tables. Any view or stored procedure that references d dropped table must be explicitly dropped by using DROP VIEW or DROP PROCEDURE statement.

9. If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter it is advisable to use DELETE.

10. You cannot use TRUNCATE TABLE on tables that are referenced by a FOREIGN KEY constraint. Instead, use DELETE statement without a WHERE clause.

11. You can truncate a table having a foreign key that references itself.

12. You cannot use TRUNCATE TABLE on tables that participate in an indexed view. Better use Delete in such cases.

13. DROP TABLE cannot be used to drop a table that's referenced by a FOREIGN KEY constraint. The referencing FOREIGN KEY constraint or the referencing table must first be dropped. If both the referencing table and the table that holds the primary key are being dropped in the same DROP TABLE statement, the referencing table must be listed first.

14. By default, a DELETE statement always acquires an exclusive (X) lock on the table it modifies, & holds that lock until the transaction completes. With an exclusive (X) lock, no other transactions can modify data; read operations can take place only with the use of the NOLOCK hint or read uncommitted isolation level. You can specify table hints to override this default behavior for the duration of the DELETE statement by specifying another locking method, however, it is not recommended and can lead to data inconsistency.

Reference : Self study, MSDN and other internet resources. 

Note: This blog is based on self study and is subject to be modified as and when I discover something new or find anything incorrect. Suggestions  and comments are welcome.

Regards,
Paras