Putting DDL statemnets (CREATE SCHEMA), in a transaction

Are T-SQL DDL Statements allowed within a transaction in SQL Server 2005?

Hi,

I'm working with some folks on a VS add-in that maps conceptual data models to physical DBMS implementations. Currently, the only target that doesn't generate a schema creation script inside a transaction is SQL Server 2005. Being a development project, having the CREATE SCHEMA script in a transaction would be useful, to avoid the need to clean out the parts of the DDL script that did run, and just start with a new iteration. When I asked why, they thought that SQL Server didn't allow DDL statements to run inside a transaction. Searching this, I could only find references that suggested you shouldn't (for performance reasons - but these are not an issue in this case).

The rules for this are likely in Books online, etc..., but searches I tried gave gave too many references, or too few.

If you know the answer or the exact reference, please pass that along. Also, if you know of an alternative way of removing a schema from a DB, without having to delete all the individual items within that schema first, please post that as well.

Thanks BRN..

Hi,

Here is some good info concerning your issue: http://www.sybase.com/detail?id=42077.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Hi and thanks for the quick response - I'll check it out. BRN..

|||

Hi,

Took a look at the SYBASE ref. That talks about MS having determined table create, alter, drop, not being allowed in a transaction due to 1) not wanting the DB structure altered 2) Performance.

As Mentioned, performance not an issue in this case. Also, I was hoping to find a MS document that stated their restrictions, and if they could be over-ridden, even if not recommended.

Still, more info than I had before, so thanks again. BRN..|||

Brian,

Not sure why you are consulting sybase documentation for SQL2005 issue.

The create schema statement can be run in a user transaction. You should be able to find most of the information @. http://msdn2.microsoft.com/en-us/library/ms189462.aspx

On the second issue, I assume you are asking if there is a way to drop a schema along with all objects contained in it in one shot. Unfortunately, this is not possible right now. You need to drop or move objects out of the schema before dropping it. We are considering this feature for a future release.

|||

Sameer,

Thanks for the link and the answer - I'll check out the link, and pass that along to the development team. The SYBASE reference was from an earlier reply, and the content was generic RDBMS, pointing out some preferences by specific vendors.

The reasons requiring schema objects to be dropped before dropping the schema are pretty easy to figure out - when a DB is populated. If there was a way to allow dropping a schema in one shot during the development phase, that would be helpful.

There might be reasons to allow dropping complete schemas in a production DB too; but that would be very dependant on how schemas (in the 2005 namespace seperate from ownership form), were utilized in the DB design. I'm looking at some of this in the project I mentioned.

Thanks again for the replies. BRN..