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

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

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
>