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

Question

I know that in SQL Server GO is considered a batch separator.

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 GO statements:

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

(source: technet documentation):

Answer 1

In the example there it is of no use whatsoever.

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

Such as CREATE PROCEDURE.

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 GO is to execute the SQL in a child batch using EXEC

Answer 2

As TechNet says, GO 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 GO 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:

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

create procedure sp_test as
begin
    select 1
end

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

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

And SSMS will show you the error:

Incorrect syntax. 'CREATE PROCEDURE' must be the only statement in a batch.

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

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

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:

INSERT INTO MyTable (...) ...
GO 10 -- run all the above 10 times!

As the answers to this SO question 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! :)

Answer 3

What is the point of having a batch separator?

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 go 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 ALTER a table to add a column, then select 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 go statements in their scripts. Further, the docs say ODBC connections may never issue a go command. It is not clear to me how a script run through ODBC would behave if it included the ALTER / SELECT 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 ALTER 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 GO 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 GO statement. I believe the initial or primary function of GO relates more to the compilation of a single execution plan, as mentioned in point 1 - otherwise the keyword may as well be something like REPEAT 10 - but repeat what? The batch. Without GO signifying a batch, a repeat command could only ever repeat the prior single statement. Therefore GO is a nice way to repeat batches.

Reference

All of this comes from trying to understand the MS documentation on GO. 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 Rules for Using Batches mentioned by Microsoft in the GO 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 CREATE TABLE you might have an INSERT into that table. That INSERT 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 ALTER to a table is followed by a SELECT and that it needs to re-compile the SELECT (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:

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

• A table cannot be changed and then the new columns referenced in the same batch.

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

Answer 4

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

create procedure [procedurename]
(parameters)
as begin

select prefname, lastname from people

end

go

grant execute on [procedurename] to [username]