Using the “GO” Command in SQL Server

Using the “GO” Command in SQL Server

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
This post discusses the “GO” command in SQL Server.
Before proceeding with explaining GO, it is first necessary to understand what a “Batch” is.

As described in MSDN BOL, a Batch is a group of one or more Transact-SQL statements sent at the same time from an application to SQL Server for execution. SQL Server compiles the statements of a batch into an execution plan.

GO is a command used for signaling the end of a batch. Note that it is not a T-SQL statement.
This command is recognized by the following SQL Server utilities:

  • sqlcmd
  • osql
  • SQL Server Management Studio Code editor

Using GO, you can specify multiple batches within your T-SQL code.
So, for an example, instead of having different files containing T-SQL code and separately executing each of it, you can just use a single file/code and separate the batches using GO. This is useful in many cases.

An example of some T-SQL code (in SQL Server Management Studio Code Editor) featuring batches is the following:

———— Example 1————
–BATCH 1
USE [AdventureWorks]
GO

–BATCH 2
DECLARE @STRING1 AS VARCHAR(50)
SET @STRING1=’BATCH 2′
SELECT @STRING1 AS RESULT
GO

–BATCH 3
DECLARE @STRING2 AS VARCHAR(50)
SET @STRING2=’BATCH 3′
SELECT @STRING2 AS RESULT
GO
———————————

The above T-SQL code features three batches.
The first batch instructs SQL Server Database Engine to use the “AdventureWorks” database.
The second batch declares a varchar variable, sets a value for it, and prints its value, and the third one does exactly the same with another variable.

So, in this example, the SQL Server Management Studio Code Editor will separately send the three bathes to SQL Server Database Engine for processing.

Note that by the time there are no dependencies between the batches, the above code is executed without any problems.

Now, consider the following code:

———— Example 2————
–BATCH 1
USE [AdventureWorks]
GO

–BATCH 2
DECLARE @STRING1 AS VARCHAR(50)
DECLARE @STRING2 AS VARCHAR(50)

SET @STRING1=’BATCH 2′
SET @STRING2=’BATCH 3′

SELECT @STRING1 AS RESULT
GO

–BATCH 3
SELECT @STRING2 AS RESULT
GO
———————————

Again, the above code features three batches. Though you can see that the variable @STRING2 is declared in Batch 2 but the SELECT statement from within Batch 3 also tries to access it for printing its value. Though, by the time @STRING2 is defined in Batch 2, consequently it will be only available for T-SQL Statements belonging to the scope of Batch 2.

Any other T-SQL statement trying to access it will result to a runtime error because @STRING2 is only visible within Batch 2.

Based on the above example, if you try to execute the entire T-SQL code you will get the following error message:

Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable “@STRING2”.

GO is very useful in SQL Server. Though, you need to use it only when is really needed.
So, you need to keep in mind that along with defining a batch using the GO command, you define the scope of that specific piece of T-SQL code.


Recommended eBooks on SQL Server:

Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.

3 thoughts on “Using the “GO” Command in SQL Server

  1. Allan

    Is it possible to insert some special character into the SQL Statement to allow for the GO Command to be on a single line?

    I need to place my sql statements into a single excel cell to have them run properly with an interface I use, and the statements use a go command.

  2. azuremarine

    The behavior of "RETURN" is worth noting as well.

    In SSMS, "RETURN" works within a batch when executing code on screen, but then SSMS will continue executing subsequent batches.

    I believe this behavior also extends into SQLCMD and the ":R" command, where it becomes a bit more confusing. This is because there may well be more than one "batch" within a single ":R" file. I believe RETURN will still only "return" from a single "inner" batch; it will not return from the ":R" file as a whole.

    I've seen some posts on other forums where people believed that RETURN "does not work", but it actually DOES work, but only with a batch.

    It would be useful to have a "STOP" command for SQLCMD and SSMS; apparently some other tools have such a capability.