Transactions and DDL statements! Ambiguity/Problem

Question

Transactions and DDL statements! Ambiguity/Problem

Abrar Ahmad_

SSCarpal Tunnel

Points: 4244
More actions
July 15, 2011 at 4:46 am

#241505

[font="Verdana"]DDL statements within transaction/s are supported in SQL Server!
I have a script in which multiple GO and multiple transaction are begin committed/rollbacked!
My question is: Do rollback functionality of DDL statements always results successful or there are some scenarios in which Rollback is some what said be partial?
Please share your experiences.
Thanks
[/font]

Viewing 7 posts - 1 through 6 (of 6 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Rollbacks are complete. Other than table variables, I can't think of any DDL operations within a database that can't be rolled back

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Do DDL statements always give you an implicit commit, or can you get an implicit rollback?

http://stackoverflow.com/questions/730621/do-ddl-statements-always-give-you-an-implicit-commit-or-can-you-get-an-implicit

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Welsh Corgi (7/15/2011)
Do DDL statements always give you an implicit commit, or can you get an implicit rollback?
http://stackoverflow.com/questions/730621/do-ddl-statements-always-give-you-an-implicit-commit-or-can-you-get-an-implicit

That question is tagged Oracle and the answers are Oracle-specific. Can't draw conclusions as to how SQL Server will behave from how Oracle behaves.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

GilaMonster (7/15/2011)
Welsh Corgi (7/15/2011)
Do DDL statements always give you an implicit commit, or can you get an implicit rollback?
http://stackoverflow.com/questions/730621/do-ddl-statements-always-give-you-an-implicit-commit-or-can-you-get-an-implicit
That question is tagged Oracle and the answers are Oracle-specific. Can't draw conclusions as to how SQL Server will behave from how Oracle behaves.

I was wondering about that.

Thanks for the correction.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Abrar Ahmad_ SSCarpal Tunnel Points: 4244 More actions · Answer 5

GilaMonster (7/15/2011)
Rollbacks are complete. Other than table variables, I can't think of any DDL operations within a database that can't be rolled back

[font="Verdana"]Really helpful!

Thanks[/font]

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

I just tested a DDL Rollback on a Create Table Statement and I was able to Rollback the Transaction.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/