March 1, 2011 at 5:57 am
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
March 1, 2011 at 6:13 am
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:
March 1, 2011 at 7:22 am
Hey thanks HowardW.
I got the details and now I have updated myself 😛
Regards,
AJ
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply