Is it possible to run multiple DDL statements inside a transaction (within SQL Server)?

Question

I'm wondering if it is possible to run multiple DDL statements inside a transaction. I'm specially interested on SQL Server, even though answers with other databases (Oracle, PostgreSQL at least) could also be interesting.

I've been doing some "CREATE TABLE" and "CREATE VIEW" for the created table inside a transaction and there seems to be some inconsistencies and I'm wondering if the DDLs shouldn't be done inside the transaction...

I could probably move the DDL outside the transaction but I'd like to get some reference for this. What I have found this far:

• MSDN page Isolation Levels in the Database Engine tells clearly that there are restrictions on what DDL operations can be performed in an explicit transaction that is running under snapshot isolation - but I'm not using snapshot isolation and this should result as an error.
  • This could be interpreted so that DDL operations can be performend in an explicit transaction under different isolation levels?
• Oracle® Database Gateway for SQL Server User's Guide#DDL Statements states that only one DDL statement can be executed in a given transaction - is this valid also for SQL Server used straight?

For Oracle:

• Within SO question Unit testing DDL statements that need to be in a transaction it is said that Oracle does implicit commit for a DDL statement? (even though no references)

If it matters something, I'm doing this with Java through the JTDS JDBC driver.

b.r. Touko

Answer 1

I know most databases have restrictions, but Postgres doesn't. You can run any number table creations, column changes and index changes in a transaction, and the changes aren't visible to other users unit COMMIT succeeds. That's how databases should be! :-)

As for SQL Server you can run DDL inside of a transaction, but SQL Server does not version metadata, and so changes would be visible to others before the transaction commits. But some DDL statements can be rolled back if you are in a transaction, but for which ones work and which ones don't you'll need to run some tests.