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

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

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

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

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

Related

Recent Posts