Sql Server: Do I Need to Use Go Statements Between Batches

SQL Server: Do I need to use GO statements between batches?

They're not strictly required - they're just instructions for the SQL Server Management Studio to execute the statements up to this point now and then keep on going. GO is not a T-SQL keyword or anything - it's just an instruction that works in SSMS.

Sometimes, you need a GO - e.g. if you add a column to a table, and then want to select it again, you need to have a GO between the adding of the column, and the query of it.

E.g. if you try to execute this, you'll get errors from SSMS:

ALTER TABLE (sometable) ADD DateTimeStamp DATETIME

SELECT ID, DateTimeStamp FROM (sometable) WHERE ID > 5

Results in:

Msg 207, Level 16, State 1, Line 9
Invalid column name 'datetimestamp'.

The point is: SSMS is trying to verify the whole statement at once, but on the SELECT statement, it will complain about the missing DateTimeStamp column.

ALTER TABLE (sometable) ADD DateTimeStamp DATETIME
GO       

SELECT ID, DateTimeStamp FROM (sometable) WHERE ID > 5

If you put a GO between the two statements, it'll work, because SSMS won't parse and verify the whole statement ahead of time - it will do the first part, and then only parse the second (after the GO).

But other than situations like this one, GO is hardly ever required.

The real function of the GO Statement in SQL?

GO is used to divide a script into multiple batches.

The word GO is not a sql statement. It is understood by the SQL batch processor (for example SSMS) not by SQL Server.

Simply put, if GO appears on a line on its own, SSMS sends each section delimited by GO as a separate batch. SQL Server never sees the GO lines, only the SQL between them.

Because SQL Server has a syntactic rule that stored procedures must be defined in a batch on their own, you will often find database creation scripts which use GO to delimit the batches so that multiple stored procedures can be created from one script. However it is the client software which understands GO and divides the batches, not SQL server.

In SQL Server, when should you use GO and when should you use semi-colon ;?

GO only relates to SSMS - it isn't actual Transact SQL, it just tells SSMS to send the SQL statements between each GO in individual batches sequentially.

The ; is a SQL statement delimiter, but for the most part the engine can interpret where your statements are broken up.

The main exception, and place where the ; is used most often is before a Common Table Expression Statement.

What is the use of GO in SQL Server Management Studio & Transact SQL?

It is a batch terminator, you can however change it to whatever you want

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

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

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
>

Can we use 'GO' multiple times in SQL Transaction?

You are mixing concepts. GO is not a Transact-SQL concept, not part of the language, and not understood by SQL Server. GO is the tools batch delimiter. sqlcmd.exe and SSMS both are using, by default, GO as the batch delimiter. The batch delimiter is used to identify the individual batches inside the SQL source file. The client tool sends to the server one batch at a time (of course, omitting the delimiter).

Transactions can span batches. TRY/CATCH blocks cannot. CREATE/ALTER statements must be the only statement in a batch (comments are not statements, and statements contained in a function procedure body are,well, contained).

Something similar to what you want to do can be achieved by starting a transaction and abortign the execution on first error (-b at sqlcmd.exe start, or use :on error exit in SSMS).

But doing DDL inside long transactions is not going to work. Specially if you plan to mix it with DML. Most corruptions I had to investigate come from this combination (Xact, DDL + DML, rollback). I strongly recommend against it.

The sole way to deploy schema updates safely is to take a backup, deploy, restore from backup if something goes wrong.

Note that what Dan recommends (dynamic SQL) works because sp_executesql starts a new, inner, batch. This batch will satisfy the CREATE/ALTER restrictions.

Should we end stored procedures with the GO statement?

The statement go, per the documentation

Signals the end of a batch of Transact-SQL statements to the SQL Server utilities.

...

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql
utilities and SQL Server Management Studio Code editor.

SQL Server utilities interpret GO as a signal that they should send the current batch
of Transact-SQL statements to an instance of SQL Server. The current batch of statements
is composed of all statements entered since the last GO, or since the start of the
ad-hoc session or script if this is the first GO.

A Transact-SQL statement cannot occupy the same line as a GO command. However, the line
can contain comments.

Users must follow the rules for batches. For example, any execution of a stored procedure
after the first statement in a batch must include the EXECUTE keyword. The scope of
local (user-defined) variables is limited to a batch, and cannot be referenced after a
GO command.

A stored procedure definition, per the documentation for create procedure, comes with restrictions. it must be the first (and only) statement in the batch:

The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in
a single batch.

That means the body of stored procedure ends with the batch. Adding GO in your source file is good practice. Especially since it's common to do things prior to and following the creation of a stored procedure. You'll often see source files that look something like this:

if (object_id('dbo.foobar') is not null ) drop procedure dbo.foobar
GO
-- dbo.foobar --------------------------------------------
-- 
-- This stored procedure does amazing and wonderful things
----------------------------------------------------------
create procedure dbo.foobar
as

   ...
   {a sequence of amazing and wonderful SQL statements}
   ...
   return 0
GO

grant execute on dbo.foobar to some_schema
GO

And the value for GO is adjustable in Sql Server Management Studio's options. If you'd like to use something like jump instead of go, you can (bearing in mind that you're almost certainly going to give yourself grief in doing so.).

---

Related Topics