Systems Engineering and RDBMS

Truncate can(not) be rolled back – Behavior is different between Oracle and SQL Server

Posted by decipherinfosys on April 16, 2009

Yesterday, while interviewing a candidate for a mid-level DBA role for SQL Server, she said that the truncate command cannot be rolled back.  Almost every candidate that we have interviewed for this position mentioned the same two things – “Truncate command is a non-logged operation and hence it cannot be rolled back in a transaction”.  That is not true in the case of SQL Server but it is true in the case of Oracle.

We had covered the differences between Truncate and Delete commands before – you can read more on that here.   Let’s just try this out rather than making statements:

SQL Server:

CREATE TABLE dbo.TEST_TRUNCATE (COL1 INT IDENTITY, COL2 NVARCHAR(100));
SET NOCOUNT ON
GO
INSERT INTO dbo.TEST_TRUNCATE (COL2) VALUES (‘TEST TRUNCATE’);
GO

BEGIN TRAN
/*Show me the count of the records before truncate*/
SELECT COUNT(*) FROM dbo.TEST_TRUNCATE
TRUNCATE TABLE dbo.TEST_TRUNCATE
/*Show me the count of the records now*/
PRINT ‘INSIDE THE TRANSACTION’
SELECT COUNT(*) FROM dbo.TEST_TRUNCATE
/*Rollback now*/
ROLLBACK TRAN

/*Show me the count of the records now*/
PRINT ‘OUTSIDE THE TRANSACTION’
SELECT COUNT(*) FROM dbo.TEST_TRUNCATE

You will see that the output will be:
———–
1                        ==> This is before the truncate is done within the transaction.

INSIDE THE TRANSACTION

———–
0                       ==> This is after the truncate is done within the transaction.

OUTSIDE THE TRANSACTION

———–
1                        ==> This is outside the transaction after the rollback is done.

So, the rollback was successful and we rolled back the data that was truncated using the truncate command.  Understand that truncate is a minimally logged operation – don’t say that it is not logged at all.  Each  row that is removed is not logged but the page de-allocations are still logged.

Now, in the case of Oracle, this is not the case.  In the case of Oracle, as Mark Roddy has pointed out in the comment below, you cannot rollback a truncate command.   In the case of Oracle, Truncate command is treated as a DDL statement and like any other DDL command in Oracle, an implicit commit is performed after execution of the statement.

So, part of the reason for the confusion out there is because the behavior is different among different RDBMS.   So, the next time you are interviewing someone and they say that Truncate cannot be rolled back, ask them whether they mean this in SQL Server or Oracle?

Resources:

  • SQL Server BOL entry on Truncate – here.
  • Truncate vs Delete – here.
  • Oracle Documentation pointing out that truncate cannot be rolled back – here.
  • Don Burleson’s post on Truncate – here.

5 Responses to “Truncate can(not) be rolled back – Behavior is different between Oracle and SQL Server”

  1. Mark Roddy said

    This varies by DB engine, correct? The oralce docs specifically state “You cannot roll back a TRUNCATE statement”. I was not able to reproduce these results via sql*plus.

    • Yes Mark. Thanks for your comment. In the case of Oracle, the behavior is different. We have added that to the post above and have also included the reason why it is different and the references to Oracle Docs.

  2. Mark Roddy said

    Thanks for the clarification! The wikipedia page on truncate has a pretty good break down by vendor. Also, according to wikipedia the truncate statement wasn’t official part of the SQL standard until SQL:2008, but I can’t seem to find a copy of this standard to see if there are any transactional semantics to it.

  3. C. Vecchio said

    This test does not work for SYBASE ASE 15.

    “TRUNCATE TABLE command not allowed within multi-statement transaction.”

    This is the same query with nothing but the TRUNCATE in the begin/end tran pair.

    CREATE TABLE dbo.TEST_TRUNCATE (COL1 INT IDENTITY, COL2 NVARCHAR(100))
    SET NOCOUNT ON
    GO
    INSERT INTO dbo.TEST_TRUNCATE (COL2) VALUES (‘TEST TRUNCATE’)
    GO

    SELECT COUNT(*) FROM dbo.TEST_TRUNCATE

    BEGIN TRAN
    /*Show me the count of the records before truncate*/
    TRUNCATE TABLE dbo.TEST_TRUNCATE
    /*Show me the count of the records now*/
    — PRINT ‘INSIDE THE TRANSACTION’
    — SELECT COUNT(*) FROM dbo.TEST_TRUNCATE
    /*Rollback now*/
    ROLLBACK TRAN

    /*Show me the count of the records now*/
    PRINT ‘OUTSIDE THE TRANSACTION’
    SELECT COUNT(*) FROM dbo.TEST_TRUNCATE

    This infers that since TRUNCATE is not part of a transaction it cannot be rolled back either, similar to ORACLE.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: