T-SQL GO Statement

Question

I have read and read over MSDN, etc. Ok, so it signals the end of a batch.

What defines a batch? I don't see why I need go when I'm pasting in a bunch of scripts to be run all at the same time.

I've never understood GO. Can anyone explain this better and when I need to use it (after how many or what type of transactions)?

For example why would I need GO after each update here:

 UPDATE [Country]
   SET [CountryCode] = 'IL'
 WHERE code = 'IL'

 GO

 UPDATE [Country]
   SET [CountryCode] = 'PT'
 WHERE code = 'PT'

Answer 1

Sometimes there is a need to execute the same command or set of commands over and over again. This may be to insert or update test data or it may be to put a load on your server for performance testing. Whatever the need the easiest way to do this is to setup a while loop and execute your code, but in SQL 2005 there is an even easier way to do this.

Let's say you want to create a test table and load it with 1000 records. You could issue the following command and it will run the same command 1000 times:

CREATE TABLE dbo.TEST (ID INT IDENTITY (1,1), ROWID uniqueidentifier)
GO
INSERT INTO dbo.TEST (ROWID) VALUES (NEWID()) 
GO 1000

source: http://www.mssqltips.com/tip.asp?tip=1216

Other than that it marks the "end" of an SQL block (e.g. in a stored procedure)... Meaning you're on a "clean" state again... e.G: Parameters used in the statement before the code are reset (not defined anymore)

Answer 2

GO is not properly a TSQL command. Instead it's a commant to the client of an SQL server (Sybase or Microsoft's - not sure about what Oracle does), signalling it 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
>

Answer 3

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 it's 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

Answer 4

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.