Transactional Data Defination Language in SQL Server 2005
time to read 3 min | 415 words

Did you know that you can have transactions on DDL as well? I run into this more or less by accident, and it is very cool. Check this out:

BEGIN TRANSACTION

    CREATE TABLE Foo( Num int)
    INSERT INTO Foo VALUES (1)
    INSERT INTO Foo VALUES (2)

    SELECT * FROM Foo

ROLLBACK

SELECT

 object_id(N'Foo') as FooId

The results of this query are:

Num
-----
1
2

FooId
------
NULL

The CREATE TABLE statement was rolled back! I usually don't approve of using DDL in the usual course of things, but what really impressed me was this query:

CREATE

 TABLE Foo( Num int)
INSERT INTO Foo VALUES (1)
INSERT INTO Foo VALUES (2)

BEGIN

 TRANSACTION
   TRUNCATE TABLE Foo
   SELECT COUNT(*) FooCount FROM Foo
ROLLBACK

SELECT

 COUNT(*) FooCount FROM Foo
DROP TABLE Foo

The results of this query are even more impressive to me:

FooCount
----------
0

FooCount
---------
2

I find it amazing, period.

Tweet Share Share 0 comments
Tags: