问题描述

我们可以在一个 SQL 事务中多次使用 GO 语句吗?我有一个很长的 T-SQL 脚本，我想在 SQL Transaction 中运行它.如果一切顺利，那么我会提交，否则会回滚.

Can We use GO statement mutiple times in a SQL Transaction. I am having a long T-SQL script and I want to run it in a SQL Transaction. If all goes well then I will commit otherwise will rollback.

但是，在运行该查询时，我遇到了类似'create function must be the only statement in the batch'的错误.因为我正在创建和删除许多函数和过程.

But, While running that query I got error like 'create function must be the only statement in the batch'. As I am creating and dropping many Functions and Procedures in that.

我没有在脚本的任何地方使用 GO.我的问题是 - 我可以在那个长脚本中多次使用 GO 语句.因为，GO 创建了一个批处理，如果批处理第一次执行成功，但下次执行失败，那么rollback transaction 语句是否能够真正回滚已执行的事务?

I have not used GO anywhere in the script. My question is that - Can I use multiple times GO statement in that long script. Because, GO creates a batch and if batch executes successfully first time but fails next time then will rollback transaction statement be able to actually rollback that has been executed ?

我的脚本结构如下:

PRINT 'Transaction Started'
BEGIN TRY
    BEGIN TRAN

    Drop Function 
    ....
    ....
    Create Function
    ....
    ....
    Drop Procedure
    ....
    ....
    Lots of statements
    ....
    ....

    COMMIT TRAN
    PRINT 'Transaction Succeeded'
END TRY
BEGIN CATCH
    PRINT 'Transaction Failed'
    IF(@@TRANCOUNT > 0)
        ROLLBACK TRAN
END CATCH

我创建此脚本是为了在单个脚本中将一些更改从 newDB 迁移到 oldDB.

I am creating this script to migrate some changes from newDB to oldDB in a single script.

推荐答案

您正在混合概念.GO 不是 Transact-SQL 的概念，不是语言的一部分，并且不被 SQL Server 理解.GO 是工具批处理分隔符.sqlcmd.exe 和 SSMS 默认都使用 GO 作为批处理分隔符.批处理分隔符用于标识 SQL 源文件中的各个批处理.客户端工具一次一批地向服务器发送(当然省略分隔符).

You are mixing concepts. GO is not a Transact-SQL concept, not part of the language, and not understood by SQL Server. GO is the tools batch delimiter. sqlcmd.exe and SSMS both are using, by default, GO as the batch delimiter. The batch delimiter is used to identify the individual batches inside the SQL source file. The client tool sends to the server one batch at a time (of course, omitting the delimiter).

交易可以跨批次进行.TRY/CATCH 块不能.CREATE/ALTER 语句必须是批处理中唯一的语句(注释不是语句，包含在函数过程体中的语句是包含的).

Transactions can span batches. TRY/CATCH blocks cannot. CREATE/ALTER statements must be the only statement in a batch (comments are not statements, and statements contained in a function procedure body are,well, contained).

通过启动事务并在第一个错误时中止执行(-b at sqlcmd.exe start，或使用:on error exit in SSMS).

Something similar to what you want to do can be achieved by starting a transaction and abortign the execution on first error (-b at sqlcmd.exe start, or use :on error exit in SSMS).

但是在长事务中执行 DDL 是行不通的.特别是如果您打算将它与 DML 混合使用.我必须调查的大多数损坏都来自这种组合(Xact、DDL + DML、回滚).我强烈建议不要这样做.

But doing DDL inside long transactions is not going to work. Specially if you plan to mix it with DML. Most corruptions I had to investigate come from this combination (Xact, DDL + DML, rollback). I strongly recommend against it.

安全部署架构更新的唯一方法是在出现问题时进行备份、部署和恢复.

The sole way to deploy schema updates safely is to take a backup, deploy, restore from backup if something goes wrong.

请注意，Dan 推荐的(动态 SQL)之所以有效，是因为 sp_executesql 会启动一个新的内部批处理.此批次将满足 CREATE/ALTER 限制.

Note that what Dan recommends (dynamic SQL) works because sp_executesql starts a new, inner, batch. This batch will satisfy the CREATE/ALTER restrictions.

这篇关于我们可以在 SQL 事务中多次使用“GO"吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！