Using the "GO" Command in SQL Server

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:
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.

Labels: ,