Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Applying TRANSACTION for DDL statements Expand / Collapse
Author
Message
Posted Tuesday, March 1, 2011 5:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 21, 2013 12:42 AM
Points: 152, Visits: 198
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
Posted Tuesday, March 1, 2011 6:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 3, 2014 8:05 AM
Points: 1,191, Visits: 9,892
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
Posted Tuesday, March 1, 2011 7:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 21, 2013 12:42 AM
Points: 152, Visits: 198
Hey thanks HowardW.

I got the details and now I have updated myself


Regards,
AJ
Post #1071222
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse