4

Possible Duplicate:
Truncate Table Within Transaction

If it is not allowed to rollback a TRUNCATE statement then how is it rolled back in a transaction? Consider the following code:

USE tempdb
GO
-- Create Test Table
CREATE TABLE TruncateTest (ID INT)
INSERT INTO TruncateTest (ID)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
GO
-- Check the data before truncate
SELECT * FROM TruncateTest
GO
-- Begin Transaction
BEGIN TRAN
-- Truncate Table
TRUNCATE TABLE TruncateTest
GO
-- Check the data after truncate
SELECT * FROM TruncateTest
GO
-- Rollback Transaction
ROLLBACK TRAN
GO
-- Check the data after Rollback
SELECT * FROM TruncateTest
GO
-- Clean up
DROP TABLE TruncateTest
GO
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
narinder saggar
  • 131
  • 2
  • 2
  • 6
  • 1
    What exactly are you asking? In SQL Server TRUNCATE is transactional. Where did you read that it is not? –  Dec 17 '11 at 09:03
  • 4
    You have a bunch of questions that have been answered and has obviously helped you, but you have not [accepted them](http://u.sbhat.me/t6SXUH). Please do else people may be not be inclined to help you – Sathyajith Bhat Dec 17 '11 at 12:52
  • Once upon a time, the question was tagged with the [tag:oracle] tag as well as the [tag:sql-server] tag. A few days after the question was asked, the [tag:oracle] tag was removed. – Jonathan Leffler Dec 12 '19 at 03:14

3 Answers3

14

It is a fairly common SQL Server belief that Truncate Cannot Be Rolled Back Because It Is Not Logged. However the code in your question just verifies that this is in fact a myth.

TRUNCATE does entail much less logging than DELETE as it only logs the page deallocatons and some metadata updates to system tables rather than recording the entire contents of the deleted rows to the log (See a detailed breakdown of logging here).

SQL Server continues to lock the deallocated pages until the transaction commits preventing their re-allocation so ROLLBACK of a TRUNCATE simply needs to reverse these page deallocations and system table updates that are recorded in the log.

Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
6

MS SQL:

The concept that a Truncate statement cannot be rolled back is fairly commonly misunderstood.

When you execute a Truncate statement, it does not get logged in the log file as it is a DDL statement.

So if you Truncate a table, you cannot Roll Back to a point in time before the truncate.

However, in a Transaction, Rollback is permitted and functions just as any other rollback would.

The rollback referred to is not transactional rollback.

In essence: Anywhere before a COMMIT is issued, you can rollback a TRUNCATE statement. After a COMMIT, you cannot rollback the data even by using the log file. Unrecoverable.

Charl
  • 982
  • 6
  • 12
  • 1
    As this question is asked for Oracle and SQL Server you should add a note that your answer is only talking about SQL Server (in Oracle you cannot rollback a TRUNCATE) –  Dec 19 '11 at 11:20
4

In Oracle, the TRUNCATE statement terminates the current transaction before it executes (with a COMMIT, I believe). Then, because it is treated like a DDL statement, the TRUNCATE executes without possibility of rollback. And then a new transaction is started.

As I understand it, the behaviour of other DDL statements in Oracle is somewhat similar.

Some DBMS provide transactional DDL statements - and very useful it is, too.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278