SQL Server Software Tools, Online Courses. eBooks, Articles and Webcasts - SQLNetHub

Using the “GO” Command in SQL Server

by

This article, discusses the usage of the “GO” command in SQL Server.
But before proceeding with explaining GO, it is first necessary to understand what a “Batch” is.

 

What is a “Batch” in SQL Server?

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.

 

Let’s See an Example of Using the “GO” Command in SQL Server

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.

 

Example 1

An example of 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.

 

Example 2

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

 

Concluding Remarks

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.

 

 

Strengthen your SQL Server Administration Skills – Enroll to our Online Course!

Check our online course on Udemy titled “Essential SQL Server Administration Tips” (special limited-time discount included in link).

Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

 

Featured Online Courses:

 

Read Also:

 

Check our other related SQL Server Administration articles.

Subscribe to our newsletter and stay up to date!

Check out our latest software releases!

Check out our eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

Views: 6,589

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

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

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

Comments are closed.