模式修改命令是事务性的吗? [英] Are schema modifying commands transactional?

问题描述

例如，如果我删除一个表然后回滚事务是重新创建表吗?

For example, if I drop a table then roll back the transaction is the table recreated?

可以在事务中进行的架构更改有哪些限制?

What are the limits to the schema changes that can be made in a transaction?

如果以上取决于Sql Server的版本，请说...

If the above depends on the version of Sql Server, please say so...

背景
我正在考虑使用一些select into"语句来创建表，然后需要删除所有上述表作为工作流程的后续部分.

Background
I am thinking of using some "select into" statements to create tables and then need to drop ALL the above tables as a later part of the workflow.

没有一个表会超过几十行.

None of the table will have more then a few tens of rows.

推荐答案

大多数数据库对象 DDL 语句都可以是用户事务的一部分.存在一些例外，例如与数据库本身的文件相关的操作(例如 ALTER DATABASE ... ADD/REMOVE FILE).再次在服务器级别，大多数对象都可以是事务的一部分.例外是端点之类的对象，它们可能会启动或停止侦听套接字.

Most database object DDL statements can be part of an user transaction. Some exceptions exists, like operations that related to files of the database itself (like ALTER DATABASE ... ADD/REMOVE FILE). At the server level again, most objects can be part of a transaction. Exceptions are objects like endpoints, which may start or stop an listenning socket.

一般的经验法则是，如果是仅元数据操作，则它可以是事务的一部分.如果是具有外部副作用的操作(创建文件、打开套接字等)，则它不能成为事务的一部分，因为无法保证回滚.

The general rule of thumb is that if is a metadata only operation then it can be part of a transaction. If is an operation with external side effects (creates a file, opens a socket etc) then it cannot be part of a transaction because the rollback cannot be guaranteed.

绝大多数 DDL 语句只是元数据，它们所做的只是修改一些元数据目录表(例如，它们在 sys.tables 中添加行)，因此它们的行为就像任何其他事务性操作:如果事务是回滚行被删除，因此表消失".它有更多细节(例如，sys.tables 是真实内部表之上的视图，如 sys.objects$，这是由 CREATE TABLE 修改的真实表)，但在高层次上，这就是正在发生的事情.

The vast majority of DDL statements are metadata only, all they do is they modify some metadata catalog tables (eg. they add row in sys.tables) and as such they behave just like any other transacted operation: if the transaction is rolled back the row is removed hence the table 'disappears'. There are more details to it (eg. sys.tables is a view on top of the real internal tables like sys.objects$ and that is the real table being modified by an CREATE TABLE) but at a high level that's what's happening.

这篇关于模式修改命令是事务性的吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！