Applying TRANSACTION for DDL statements

  • Hi Guys,

    I had this understanding that the DDL statements are autocommit statements and if any DDL statement is written in any TRANSACTION then as the DDl statement executes all the DML before it got commit.

    I tried below script -

    CREATE TABLE AJ1 (ID INT IDENTITY(-1,1))

    --CREATE TABLE AJ2(ID INT IDENTITY(-1,-1))

    INSERT INTO AJ1 Default values

    INSERT INTO AJ1 Default values

    INSERT INTO AJ1 Default values

    BEGIN TRAN

    INSERT INTO AJ1 Default values

    INSERT INTO AJ1 Default values

    INSERT INTO AJ1 Default values

    --DROP TABLE AJ1

    CREATE TABLE AJ2(ID INT IDENTITY(-1,-1))

    INSERT INTO AJ1 Default values

    INSERT INTO AJ1 Default values

    INSERT INTO AJ1 Default values

    RollBack

    select * from AJ1

    select * from AJ2

    I am getting the below mentioned message -

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    ID

    -----------

    -1

    0

    1

    (3 row(s) affected)

    Msg 208, Level 16, State 1, Line 23

    Invalid object name 'Aj2'

    So now I am a bit confused that DDL statements can be rolledback if written in TRANSACTION.

    Any suggestion or reference.

    Regards,

    AJ

  • No, pretty much everything that happens within the scope of an individual database is part of the transaction and can be rolled back, including DDL and indeed TRUNCATE (another transaction myth).

    Have a look here for T-SQL Statements that are not allowed within a transaction:

    http://msdn.microsoft.com/en-us/library/ms191544.aspx

  • Hey thanks HowardW.

    I got the details and now I have updated myself 😛

    Regards,

    AJ

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply