SQLcommitted

DDL command with in transaction

I attended the SQL Saturday #116 event on 7^th January 2012 at Microsoft Signature Building Bangalore.

I learned a lot from this event. In the event we discussed different features of MS-SQL server.

During the event, like other forum someone asked the same question, Can we rollback a DDL statement? and during the discussion of answer to this question, one answer put me in doubt, that is if the DDL command is within Begin Tran… Rollback, it will rollback the DLL statement and if the DDL command is in between Begin Tran …. Rollback Tran it won’t rollback the DDL statement.

To clear my doubt I tried the below script and found DDL statements can be rolled back in both the cases.

Let’s find it out how it works….

Example 1: Using Begin Tran…Rollback

–Explicitly beginning the Transaction

Begin Tran

Create table Table4 (column1 int)

go

– Check Table is created or not

Select * from INFORMATION_SCHEMA.TABLES

–Rollback the transaction

Rollback

–Check Table is exist or not

Select * from INFORMATION_SCHEMA.TABLES

Output of 1^st Select statement: Before Rollback the Table4 is exist

Output of 2^nd Select statement. Here we can see the Table4 doesn’t exist

Example 2:Using Begin Tran…Rollback tran

Begin Tran

Create table Result (column1 int)

go

Insert into Result values(1)

select * from Result

Rollback tran

Select * from Result

Output:

(1 row(s) affected)

(1 row(s) affected)

Msg 208, Level 16, State 1, Line 4

Invalid object name ‘Result’.

 

 

January 8, 2012 Posted by SQLcommitted | Transaction | Transaction | Leave a Comment