Daniel Calbimonte
sqlcmd go command

SQL GO command in SQL Server

May 18, 2021 by

Introduction

If you are using SQL Server and saw a GO command in a T-SQL script and you do not know what that GO is used for, this article is for you. In this article, we will explain what the SQL GO command is when to use it, and some tips about using it.

Some history about the SQL GO command

SQL Server was based on the Sybase database (an old database system deprecated in 2014). SQL Server started with several system procedures from Sybase and after some years, it changed a lot with new different features, but the GO statement was one of the main commands in Sybase inherited by SQL Server and it is still used today.

What is a sql GO command?

The GO command is frequently used inside T-SQL code to use batches. It is not a T-SQL statement, but it is a command recognized by native SQL Server tools like the SSMS, the SQLCMD, and OSQL (SQLCMD is the command-line to handle SQL Server and osql is an old version of SQLCMD that may be removed someday).

To connect to sqlcmd you have to go to the command line and to enter with your windows credentials use the sqlcmd -E command. Then you can write sentences with the GO command which is mandatory.

sqlcmd go command

For more information about SQLCMD, refer to my other article below:

You can exit with the exit command. Osql is basically the same. In the command-line write osql -E to login:

osql go command

If you want to know a little more about osql, refer to this link:

In the command line, you need to use GO because that way, you know that the T-SQL statement ended and you know that you can execute it.

For example, this T-SQL query will show the databases. The query requires a GO at the end to inform to sqlcmd that it is the end of the batch. Without the GO, the query will not be executed.

SQL GO command syntax

The syntax for the GO command is the following:

Where number is an integer value that shows the number of times that the batch can be executed.

For more information about the SQL GO command, you can check out this Microsoft documentation.

Example for the SQL GO command with a number

The following example will insert data in a table 5 times.

By default, the number of times is 1 and it is usually used just to separate batches while the number of times feature, is not frequently used.

Differences between the semicolon and the SQL Go command

In many scenarios, you can use a semicolon or the GO command. However, the semicolon is used to separate statements and the GO command is for batches. There are some situations where the semicolon is mandatory like in some CTE scenarios or in Merge scenarios.

On the other hand, the GO command is used for some sentences with SET statements like the SET QUOTED_IDENTIFIER. Another big difference is that if you have an error in two sentences and you have a semicolon instead of the GO, the 2 sentences will not run. On the other hand, if you have 1 error in 2 batches separated by the SQL GO command, the batch will run successfully without errors.

SQL GO command in SSMS

In SSMS the GO is used by default to separate batches. If you do not like it, you can change it.

In order to change it in SSMS, go to tools>options and then go to Query Execution and look for the Batch separator. Type the type separator of your preference.

SQL Server Management Studio GO batch separator

In SSMS, the GO command is very useful if you have several sentences to execute. For example, you cannot create several stored procedures without the GO command.

If you try this, it will raise an error:

The error message will be Incorrect Syntax. The CREATE PROCEDURE must be the only statement in the batch.

If you try to execute the error message will be:

Msg 156, Level 15, State 1, Procedure p1, Line 2 [Batch Start Line 1]
Incorrect syntax near the keyword ‘procedure’

That is because a batch only supports one create procedure sentence. The next procedure creation will be invalid and the batch rejected.

To fix this problem, we use the word GO to execute 2 batches like this:

Another common use for the batch command is to move to a different database. For example, if I am in database AdventureWorks and I want to move to the database Northwind, the syntax will be this one:

Conclusions

In this article, we learned what the SQL GO command is and when to use it. We also explained different scenarios to use it using SSMS, sqlcmd. Osql is basically an old sqlcmd and the usage is the same.

We also show some examples about it. In general, you will see a lot of sentences separated by the GO command in the SQL Server documentation. It is a good practice to separate sentences in batches and sometimes, it is a must.

Daniel Calbimonte
Latest posts by Daniel Calbimonte (see all)
168 Views