blocks|key|3236789|text|在这个例子中，它没有任何用处。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3236790|但是，批处理中必须只有许多语句。|3236791|例如CREATE+PROCEDURE。|offset|length|style|CODE|3236792|此外，通常在进行模式更改(例如，向现有表中添加新列)之后，必须在不同的批处理中单独编译使用新模式的语句。|3236793|通常，提交由GO分隔的单独批处理的另一种方法是使用EXEC在子批处理中执行SQL|3236794|entityMap^0|0|0|2|G|0|0|6|2|P|4|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|R|8|@$F|S|G|T|H|I]]|9|@]|A|$]]|$1|J|3|K|5|6|7|U|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|V|8|@$F|W|G|X|H|I]|$F|Y|G|Z|H|I]]|9|@]|A|$]]|$1|N|3|-4|5|6|7|10|8|@]|9|@]|A|$]]]|O|$]]

In the example there it is of no use whatsoever.

Lots of statements must be the only ones in the batch however.

Such as <code>CREATE PROCEDURE</code>.

Also often after making schema changes (e.g. adding a new column to an existing table) statements using the new schema must be compiled separately in a different batch.

Generally an alternative to submitting separate batches separated by <code>GO</code> is to execute the SQL in a child batch using <code>EXEC</code>

blocks|key|3236800|text|正如Martain所说，诸如CREATE+PROCEDURE之类的语句必须是批处理中的唯一语句。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3236801|例如，每当我创建存储过程并向某个用户添加权限时，都会使用批处理分隔符。如果我省略了'go‘，那么我最终会得到一个在每次运行时都会授予权限的存储过程。这样，我可以同时编写它们，并确保我编写的存储过程不会在调用它们时中断。例如|3236802|create+procedure+[procedurename]
(parameters)
as+begin

select+prefname,+lastname+from+people

end

go

grant+execute+on+[procedurename]+to+[username]|code-block|syntax|javascript|3236803|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Like Martain said, statements such as CREATE PROCEDURE must be the only ones in a batch.

For example, I use batch separators whenever I create stored procedures and add permissions to a certain user. 
If I left out the 'go' then I would end up with a stored procedure that grants rights every time it runs. This way I can write them at the same time and be sure that I'm not writing stored procedures that break when I call them. For example

<pre><code>create procedure [procedurename]
(parameters)
as begin

select prefname, lastname from people

end

go

grant execute on [procedurename] to [username]
</code></pre>

blocks|key|3236815|text|作为TechNet+says，GO向SQL实用程序表示SQL批处理的结束。例如，当SQL+Server+Management+Studio遇到批处理分隔符时，它知道到目前为止的所有文本都是一个独立的SQL查询。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3236816|我们在我们的软件中使用了类似的技术。我们将所有的过程、模式脚本、数据转换等都保存在SQL脚本文件中(签入到源代码控制中)。当我们的安装程序读取其中一个脚本文件时，GO告诉我们的解析器“你可以运行你已经读过的SQL”。|3236817|像GO这样的批处理分隔符的一个很好的特性是，您可以在同一脚本中包含两个通常会导致错误的SQL查询。例如，尝试删除并在同一脚本文件中重新创建同一存储过程：|3236818|if+exists+(select+*+from+sys.procedures+where+name+=+'sp_test')
++++drop+procedure+sp_test

create+procedure+sp_test+as
begin
++++select+1
end|code-block|syntax|javascript|3236819|如果你运行上面的代码，你会得到一个错误：|3236820|3236821|消息156，级别15，状态1，过程sp_test，第5行关键字'begin‘附近的语法不正确。|blockquote|3236822|3236823|3236824|SSMS将向您显示错误：|3236825|3236826|语法不正确。“'CREATE”必须是批处理中的唯一语句。|3236827|3236828|3236829|使用批次分隔符可以帮助您解决此错误：|3236830|if+exists+(select+*+from+sys.procedures+where+name+=+'sp_test')
++++drop+procedure+sp_test
GO
create+procedure+sp_test+as
begin
++++select+1
end|3236831|例如，如果您希望源代码控制中的单个SQL脚本维护存储过程或函数，这将非常方便。我们经常使用这个模式。|3236832|您可以做的另一件有趣的事情是使用它来多次运行查询：|3236833|INSERT+INTO+MyTable+(...)+...
GO+10+--+run+all+the+above+10+times!|3236834|正如the+answers+to+this+SO+question演示的那样，您还可以将其配置为您想要的任何内容。如果你想惹恼你的同事，把批次分隔符设置成类似于"WHERE“而不是"GO”。有趣！:)|3236835|entityMap|0|LINK|mutability|MUTABLE|url|http://technet.microsoft.com/en-us/library/ms188037.aspx|1|https://stackoverflow.com/questions/2299249/what-is-the-use-of-go-in-sql-server-management-studio^0|F|2|2|C|0|0|0|1|2|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|2|V|1|0^^$0|@$1|2|3|4|5|6|7|1P|8|@$9|1Q|A|1R|B|C]]|D|@$9|1S|A|1T|1|1U]]|E|$]]|$1|F|3|G|5|6|7|1V|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|1W|8|@$9|1X|A|1Y|B|C]]|D|@]|E|$]]|$1|J|3|K|5|L|7|1Z|8|@]|D|@]|E|$M|N]]|$1|O|3|P|5|6|7|20|8|@]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|21|8|@]|D|@]|E|$]]|$1|R|3|S|5|T|7|22|8|@]|D|@]|E|$]]|$1|U|3|-4|5|6|7|23|8|@]|D|@]|E|$]]|$1|V|3|-4|5|6|7|24|8|@]|D|@]|E|$]]|$1|W|3|X|5|6|7|25|8|@]|D|@]|E|$]]|$1|Y|3|-4|5|6|7|26|8|@]|D|@]|E|$]]|$1|Z|3|10|5|T|7|27|8|@]|D|@]|E|$]]|$1|11|3|-4|5|6|7|28|8|@]|D|@]|E|$]]|$1|12|3|-4|5|6|7|29|8|@]|D|@]|E|$]]|$1|13|3|14|5|6|7|2A|8|@]|D|@]|E|$]]|$1|15|3|16|5|L|7|2B|8|@]|D|@]|E|$M|N]]|$1|17|3|18|5|6|7|2C|8|@]|D|@]|E|$]]|$1|19|3|1A|5|6|7|2D|8|@]|D|@]|E|$]]|$1|1B|3|1C|5|L|7|2E|8|@]|D|@]|E|$M|N]]|$1|1D|3|1E|5|6|7|2F|8|@]|D|@$9|2G|A|2H|1|2I]]|E|$]]|$1|1F|3|-4|5|6|7|2J|8|@]|D|@]|E|$]]]|1G|$1H|$5|1I|1J|1K|E|$1L|1M]]|1N|$5|1I|1J|1K|E|$1L|1O]]]]

As <a href="http://technet.microsoft.com/en-us/library/ms188037.aspx" rel="noreferrer">TechNet says</a>, <code>GO</code> it signifies the end of a SQL batch to the SQL utilities. For example, when SQL Server Management Studio encounters the batch separator, it knows all of the text so far is an independent SQL query.

We use a similar technique in our software. We keep all of our procs, schema scripts, data conversions, etc., in SQL script files (checked in to source control). When our installer reads one of these script files, GO tells our parser "you can run the SQL that you've already read".

The nice feature about a batch separator like <code>GO</code> is that you can include two SQL queries together in the same script that would normally cause an error. For example, try to drop and re-create the same stored procedure in the same script file:

<pre><code>if exists (select * from sys.procedures where name = 'sp_test')
 drop procedure sp_test

create procedure sp_test as
begin
 select 1
end
</code></pre>

If you run the above code, you will get an error:

<blockquote>
 Msg 156, Level 15, State 1, Procedure sp_test, Line 5 Incorrect syntax
 near the keyword 'begin'.
</blockquote>

And SSMS will show you the error:

<blockquote>
 Incorrect syntax. 'CREATE PROCEDURE' must be the only statement in a batch.
</blockquote>

Using a batch separator can help you get around this error:

<pre><code>if exists (select * from sys.procedures where name = 'sp_test')
 drop procedure sp_test
GO
create procedure sp_test as
begin
 select 1
end
</code></pre>

This is very handy if, say, you want a single SQL script in source control to maintain a stored procedure or function. We use this pattern frequently.

Another interesting thing you can do is use it to run a query multiple times:

<pre><code>INSERT INTO MyTable (...) ...
GO 10 -- run all the above 10 times!
</code></pre>

As <a href="https://stackoverflow.com/questions/2299249/what-is-the-use-of-go-in-sql-server-management-studio">the answers to this SO question</a> demonstrate, you can also configure it to whatever you want. If you want to mess with your co-workers, set the batch separator to something like "WHERE" instead of "GO". Fun! :)

blocks|key|405104|text|type|unstyled|depth|inlineStyleRanges|entityRanges|data|405105|+有批次分隔符的意义是什么？|blockquote|offset|length|style|BOLD|405106|405107|405108|在阅读了许多答案并发表了评论后，以下是我的想法。|405109|真正的问题是“拥有批处理的意义是什么？”|405110|批处理有两个含义，go的另一个用法可能会很有用：|CODE|405111|1.将批处理中的所有语句编译为一个执行计划|405112|作为一名SQL开发人员，我不知道这对您有什么影响。但事实就是如此。这意味着您不能在同一批处理中包含一些语句。例如，您不能先ALTER表以添加列，然后在同一批处理中select该列-因为在编译执行计划时，该列不存在以供选择。|405113|我认为，对于SQL+Server是否应该能够在不要求开发人员在其脚本中包含go语句的情况下自行检测到这一点，存在一个开放的争论。此外，文档还说ODBC+connection可能永远不会发出go命令。我不清楚如果包含刚才给出的ALTER+/+SELECT示例，通过ODBC运行的脚本会有什么行为。|405114|2.本地声明的变量仅存在于它们被声明为的批处理范围内|405115|这两点结合在一起有点糟糕。我有一个创建和更改DB结构(表、过程等)的脚本，我想在脚本的开头声明变量，这些变量将用于管理整个脚本的行为。只要我需要包装一个批处理(例如，由于一条ALTER语句-请参见上面的观点1+)，这些“配置”变量就会超出作用域，并且不能在脚本中进一步使用。我的解决方法是创建一个表，将配置变量持久化到表中，然后通过我的脚本一直从该表中读取，然后在最后删除该表(以防其他人遇到这种情况)。|405116|第二个含义实际上可以用于优势-如果你的脚本正在做大量的工作，而你只是想清除所有的局部变量，你可以简单地包括一个GO语句，然后声明新的变量(即.并重新使用相同的名称，如果这是您想要的)。|405117|3.+GO有一个可选参数(名为"count")，它告诉服务器将批处理操作重复多次|405118|这种用法似乎是添加到GO语句中的很好的附加功能。我认为GO的初始或主要功能更多地与编译单个执行计划有关，如第一点所述-否则关键字可能类似于REPEAT+10+-但重复什么？批次。如果不使用GO表示批处理，则重复命令只能重复前面的单个语句。因此，GO是重复批处理的好方法。|405119|参考|405120|所有这些都来自于对MS+documentation+on+GO的理解。许多其他的答案-这里和其他问题-都是针对文档的片段，但我认为文档本身无法真正解释为什么批处理有好处-因此我对一个已经得到很好评论的问题做出了贡献。|405121|附录|405122|写完上面的内容后，我确实在GO文档中找到了微软提到的Rules+for+Using+Batches。该链接页面说明执行计划由多个语句组成。它还说，可以将单个语句重新编译为新的执行计划(即由SQL+Server自动处理批处理)。因此，例如，在CREATE+TABLE语句之后，您可能有一个到该表的INSERT。在前面的语句中创建表之后，将重新编译该INSERT语句。|405123|这再次强化了这样一种想法，即SQL+Server可能会检测到表的ALTER后面跟着SELECT的情况，并且它需要重新编译SELECT+(请参见上面的第1点)，如果使用ODBC，可能就会发生这种情况(请参见上面的第1点)。|405124|这些新信息都不会改变上面给出的3点。我刚才给出的链接包含额外的阅读内容，并以“规则”结尾，它们是：|405125|405126|CREATE+DEFAULT、CREATE+FUNCTION、CREATE+PROCEDURE、CREATE+RULE、CREATE+SCHEMA、CREATE+TRIGGER和CREATE+VIEW语句不能与批处理中的其他语句组合。CREATE语句必须启动批处理。该批处理中后面的所有其他语句将被解释为第一个CREATE语句的定义的一部分。|unordered-list-item|405127|不能更改表，然后在同一批处理中引用新列。|405128|如果EXECUTE语句是批处理中的第一个语句，则不需要EXECUTE关键字。如果EXECUTE语句不是批处理中的第一个语句，则需要EXECUTE关键字。|405129|405130|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.microsoft.com/en-us/sql/t-sql/language-elements/sql-server-utilities-statements-go?view=sql-server-2017|1|https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms175502(v=sql.105)^0|0|1|D|0|0|0|0|0|K|0|9|2|0|0|L|0|1P|5|29|6|0|11|2|2M|2|34|5|3C|6|0|0|J|0|2F|5|0|1J|2|0|0|14|0|A|2|R|2|1X|9|2M|2|3E|2|0|0|2|0|9|M|0|0|0|2|0|D|2|3C|C|43|6|4T|6|Q|N|1|0|W|5|15|6|1O|6|0|0|0|0|0|0|0^^$0|@$1|2|3|-4|4|5|6|21|7|@]|8|@]|9|$]]|$1|A|3|B|4|C|6|22|7|@$D|23|E|24|F|G]]|8|@]|9|$]]|$1|H|3|-4|4|5|6|25|7|@]|8|@]|9|$]]|$1|I|3|-4|4|5|6|26|7|@]|8|@]|9|$]]|$1|J|3|K|4|5|6|27|7|@]|8|@]|9|$]]|$1|L|3|M|4|5|6|28|7|@$D|29|E|2A|F|G]]|8|@]|9|$]]|$1|N|3|O|4|5|6|2B|7|@$D|2C|E|2D|F|P]]|8|@]|9|$]]|$1|Q|3|R|4|5|6|2E|7|@$D|2F|E|2G|F|G]]|8|@]|9|$]]|$1|S|3|T|4|5|6|2H|7|@$D|2I|E|2J|F|P]|$D|2K|E|2L|F|P]]|8|@]|9|$]]|$1|U|3|V|4|5|6|2M|7|@$D|2N|E|2O|F|P]|$D|2P|E|2Q|F|P]|$D|2R|E|2S|F|P]|$D|2T|E|2U|F|P]]|8|@]|9|$]]|$1|W|3|X|4|5|6|2V|7|@$D|2W|E|2X|F|G]]|8|@]|9|$]]|$1|Y|3|Z|4|5|6|2Y|7|@$D|2Z|E|30|F|P]]|8|@]|9|$]]|$1|10|3|11|4|5|6|31|7|@$D|32|E|33|F|P]]|8|@]|9|$]]|$1|12|3|13|4|5|6|34|7|@$D|35|E|36|F|G]]|8|@]|9|$]]|$1|14|3|15|4|5|6|37|7|@$D|38|E|39|F|P]|$D|3A|E|3B|F|P]|$D|3C|E|3D|F|P]|$D|3E|E|3F|F|P]|$D|3G|E|3H|F|P]]|8|@]|9|$]]|$1|16|3|17|4|5|6|3I|7|@$D|3J|E|3K|F|G]]|8|@]|9|$]]|$1|18|3|19|4|5|6|3L|7|@]|8|@$D|3M|E|3N|1|3O]]|9|$]]|$1|1A|3|1B|4|5|6|3P|7|@$D|3Q|E|3R|F|G]]|8|@]|9|$]]|$1|1C|3|1D|4|5|6|3S|7|@$D|3T|E|3U|F|P]|$D|3V|E|3W|F|P]|$D|3X|E|3Y|F|P]|$D|3Z|E|40|F|P]]|8|@$D|41|E|42|1|43]]|9|$]]|$1|1E|3|1F|4|5|6|44|7|@$D|45|E|46|F|P]|$D|47|E|48|F|P]|$D|49|E|4A|F|P]]|8|@]|9|$]]|$1|1G|3|1H|4|5|6|4B|7|@]|8|@]|9|$]]|$1|1I|3|-4|4|5|6|4C|7|@]|8|@]|9|$]]|$1|1J|3|1K|4|1L|6|4D|7|@]|8|@]|9|$]]|$1|1M|3|1N|4|1L|6|4E|7|@]|8|@]|9|$]]|$1|1O|3|1P|4|1L|6|4F|7|@]|8|@]|9|$]]|$1|1Q|3|-4|4|5|6|4G|7|@]|8|@]|9|$]]|$1|1R|3|-4|4|5|6|4H|7|@]|8|@]|9|$]]]|1S|$1T|$4|1U|1V|1W|9|$1X|1Y]]|1Z|$4|1U|1V|1W|9|$1X|20]]]]

<blockquote>
 What is the point of having a batch separator?
</blockquote>

Having read many of the answers, and contributed to comments, here is what I think. 

The real question is "What is the point of having a batch?"

There are 2 implications of batching that have some meaning, and there is an additional usage of <code>go</code> that can be useful:

1. All statements in a batch are compiled into a single execution plan

How this impacts you, as a SQL developer, I don't know. But there it is. The implication of this is that you can't have some statements within the same batch. For example, you cannot <code>ALTER</code> a table to add a column, then <code>select</code> that column in the same batch - because while compiling the execution plan, that column does not exist for selecting.

I think there is an open argument as to whether SQL Server should be able to detect this by itself without requiring developers to include <code>go</code> statements in their scripts. Further, the docs say ODBC connections may never issue a <code>go</code> command. It is not clear to me how a script run through ODBC would behave if it included the <code>ALTER</code> / <code>SELECT</code> example just given.

2. Locally declared variables exist only within the scope of the batch in which they were declared

These two points combined kind of suck. I have a script that creates and alters DB structures (tables, procedures, etc) and I want to declare variables at the start of the script that will be used to govern the behaviour of the script overall. As soon as I need to wrap up a batch (due to, say, an <code>ALTER</code> statement - see my point 1, above), those "config" variables fall out of scope and can't be used further down the script. My workaround is to create a table, persist the config variables into the table, then read from that table all the way through my script, then drop the table at the end (in case anyone else is facing this).

This second implication can actually be used to advantage - if your script is doing a lot of work and you simply want to clear out all your local variables, you can simply include a <code>GO</code> statement and then declare new variables (ie. and re-use the same names, if that's what you want).

3. GO has an optional parameter (named "count") which tells the server to repeat the batch actions multiple times

This usage seems to be nice additional functionality added on to the <code>GO</code> statement. I believe the initial or primary function of <code>GO</code> relates more to the compilation of a single execution plan, as mentioned in point 1 - otherwise the keyword may as well be something like <code>REPEAT 10</code> - but repeat what? The batch. Without <code>GO</code> signifying a batch, a repeat command could only ever repeat the prior single statement. Therefore <code>GO</code> is a nice way to repeat batches.

Reference

All of this comes from trying to understand the <a href="https://docs.microsoft.com/en-us/sql/t-sql/language-elements/sql-server-utilities-statements-go?view=sql-server-2017" rel="noreferrer">MS documentation on GO</a>. Many of the other answers - here, and on other questions - pick at pieces of the documentation but I think the documentation itself fails to really explain why there is a benefit to batching in the first place - hence my contribution to an already well-commented question.

Addendum

After writing the above, I did find the <a href="https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms175502(v=sql.105)" rel="noreferrer">Rules for Using Batches</a> mentioned by Microsoft in the <code>GO</code> documentation. The linked page explains that an execution plan consists of multiple statements. It also says that individual statements can be re-compiled into a new execution plan (ie by SQL Server, while processing the batch, automatically). So for example, following a statement to <code>CREATE TABLE</code> you might have an <code>INSERT</code> into that table. That <code>INSERT</code> statement will be recompiled after the table has been created in the prior statement. 

This re-enforces the idea that SQL Server probably could detect those scenarios where an <code>ALTER</code> to a table is followed by a <code>SELECT</code> and that it needs to re-compile the <code>SELECT</code> (see my point 1 above), and possibly this is exactly what happens if using ODBC (see point 1 above).

None of this new information alters the 3 points given above. The link I just gave contains additional reading and ends with "the rules", which are these:

<ul>
<li>CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch. The CREATE statement must start the batch. All other statements that follow in that batch will be interpreted as part of the definition of the first CREATE statement.</li>
<li>A table cannot be changed and then the new columns referenced in the same batch.</li>
<li>If an EXECUTE statement is the first statement in a batch, the EXECUTE keyword is not required. The EXECUTE keyword is required if the EXECUTE statement is not the first statement in the batch.</li>
</ul>

I know that in SQL Server <code>GO</code> <a href="https://stackoverflow.com/questions/2299249/what-is-the-use-of-go-in-sql-server-management-studio">is considered a batch separator</a>.

My question is: What is the point of having a batch separator? What benefit does it give you and why would you want to use it?

Example: I've often seen it used in SQL code as follows and I can't see why it would be considered a best practice. As far as I can tell the code would the same without all the <code>GO</code> statements:

<pre><code>USE AdventureWorks2012;
GO
BEGIN TRANSACTION;
GO
IF @@TRANCOUNT = 0
BEGIN
 SELECT FirstName, MiddleName 
 FROM Person.Person WHERE LastName = 'Adams';
 ROLLBACK TRANSACTION;
 PRINT N'Rolling back the transaction two times would cause an error.';
END;
ROLLBACK TRANSACTION;
PRINT N'Rolled back the transaction.';
GO
</code></pre>

(source: <a href="http://technet.microsoft.com/en-us/library/ms187807.aspx" rel="noreferrer">technet documentation</a>):

SQL Server: What are batching statements (i.e. using "GO") good for?

数据转换

SQL Server

服务器

我在SQL Server GO 中知道这一点。我的问题是：，有批次分隔符的意义是什么？，它给你带来了什么好处，你为什么要使用它？示例:我经常在SQL代码中看到它的使用，如下所示，我不明白为什么它会被认为是最佳实践。据我所知，如果没有所有的GO语句，代码将是相同的：USE AdventureWorks2012;GOBEG...

问SQL Server:批处理语句(即使用"GO")有什么好处？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server:批处理语句(即使用"GO")有什么好处？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server:批处理语句(即使用"GO")有什么好处？
EN