What is a "batch", and why is GO used?

GO is not properly a TSQL command.

Instead it's a command to the specific client program which connects to an SQL server (Sybase or Microsoft's - not sure about what Oracle does), signalling to the client program that the set of commands that were input into it up till the "go" need to be sent to the server to be executed.

Why/when do you need it?

  • GO in MS SQL server has a "count" parameter - so you can use it as a "repeat N times" shortcut.

  • Extremely large updates might fill up the SQL server's log. To avoid that, they might need to be separated into smaller batches via go.

    In your example, if updating for a set of country codes has such a volume that it will run out of log space, the solution is to separate each country code into a separate transaction - which can be done by separating them on the client with go.

  • Some SQL statements MUST be separated by GO from the following ones in order to work.

    For example, you can't drop a table and re-create the same-named table in a single transaction, at least in Sybase (ditto for creating procedures/triggers):

> drop table tempdb.guest.x1          
> create table tempdb.guest.x1 (a int)
> go
  Msg 2714, Level 16, State 1
  Server 'SYBDEV', Line 2
  There is already an object named 'x1' in the database.   
  
> drop table tempdb.guest.x1          
> go
> create table tempdb.guest.x1 (a int)
> go
>

GO is not a statement, it's a batch separator.

The blocks separated by GO are sent by the client to the server for processing and the client waits for their results.

For instance, if you write

DELETE FROM a
DELETE FROM b
DELETE FROM c

, this will be sent to the server as a single 3-line query.

If you write 

DELETE FROM a
GO
DELETE FROM b
GO
DELETE FROM c

, this will be sent to the server as 3 one-line queries.

GO itself does not go to the server (no pun intended). It's a pure client-side reserved word and is only recognized by SSMS and osql.

If you will use a custom query tool to send it over the connection, the server won't even recognize it and issue an error.


Many command need to be in their own batch, like CREATE PROCEDURE

Or, if you add a column to a table, then it should be in its own batch. If you try to SELECT the new column in the same batch it fails because at parse/compile time the column does not exist.

GO is used by the SQL tools to work this out from one script: it is not a SQL keyword and is not recognised by the engine.

These are 2 concrete examples of day to day usage of batches.

Edit: In your example, you don't need GO...

Edit 2, example. You can't drop, create and permission in one batch... not least, where is the end of the stored procedure?

IF OBJECT_ID ('dbo.uspDoStuff') IS NOT NULL
    DROP PROCEDURE dbo.uspDoStuff
GO
CREATE PROCEDURE dbo.uspDoStuff
AS
SELECT Something From ATable
GO
GRANT EXECUTE ON dbo.uspDoStuff TO RoleSomeOne
GO

Tags:

Tsql

Recent Posts