是否可以在事务中(在 SQL Server 中)运行多个 DDL 语句? [英] Is it possible to run multiple DDL statements inside a transaction (within SQL Server)?

问题描述

我想知道是否可以在一个事务中运行多个 DDL 语句.我对 SQL Server 特别感兴趣，尽管其他数据库(至少是 Oracle、PostgreSQL)的答案也可能很有趣.

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.

我一直在为事务内创建的表做一些创建表"和创建视图"，似乎存在一些不一致，我想知道 DDL 是否不应该在事务内完成...

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...

我可能可以将 DDL 移到事务之外，但是我想为此得到一些参考.到目前为止我发现了什么:

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 页面 数据库引擎中的隔离级别 清楚地表明对于在快照隔离下运行的显式事务中可以执行的 DDL 操作有限制 - 但我没有使用快照隔离，这应该会导致错误.
  • 这可以解释为 DDL 操作可以在不同隔离级别下的显式事务中执行吗?
  • 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?

    对于甲骨文:

    • 在 SO 问题中 单元测试 DDL 语句需要在事务中 据说Oracle 对DDL 语句进行了隐式提交?(即使没有参考)
    • 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)

    如果这很重要，我将通过 JTDS JDBC 驱动程序使用 Java 执行此操作.

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

    b.r.东子

    推荐答案

    我知道大多数数据库都有限制，但 Postgres 没有.您可以在事务中运行任意数量的表创建、列更改和索引更改，并且更改对其他用户不可见，单元 COMMIT 成功.数据库应该是这样的！:-)

    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! :-)

    对于 SQL Server，您可以在事务内运行 DDL，但是 SQL Server不版本元数据，因此在事务提交之前其他人可以看到更改.但是 某些 DDL 语句可以在您处于事务中时回滚，但是对于哪些有效，哪些无效，您需要运行一些测试.

    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.

    这篇关于是否可以在事务中(在 SQL Server 中)运行多个 DDL 语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！