Applying TRANSACTION for DDL statements

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Applying TRANSACTION for DDL statements

Applying TRANSACTION for DDL statements

Rate Topic

Display Mode

Topic Options

Author

Message

AJ07

Posted Tuesday, March 01, 2011 5:57 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:38 AM
Points: 144, Visits: 166

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

Post #1071141

HowardW

Posted Tuesday, March 01, 2011 6:12 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 6:36 AM
Points: 1,035, Visits: 7,671

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

Post #1071156

AJ07

Posted Tuesday, March 01, 2011 7:22 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:38 AM
Points: 144, Visits: 166

Hey thanks HowardW.

I got the details and now I have updated myself Tongue

Regards,
AJ

Post #1071222

« Prev Topic | Next Topic »

Permissions