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.
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:
If you want to know a little more about osql, refer to this link:
- SQL Server utilities you probably didn’t know about – SQLdiag, osql and SqlLocalDB (osql is in the middle of the article)
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.
|
1 2 |
select name from sys.databases GO |
SQL GO command syntax
The syntax for the GO command is the following:
|
1 |
GO number |
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.
|
1 2 |
INSERT INTO dbo.vote(result) values(‘President’)b GO 5 |
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.
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:
|
1 2 |
create procedure p1 as select 'first stored procedure' create procedure p1 as select 'second stored procedure' |
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:
|
1 2 3 |
create procedure p1 as select 'first stored procedure' GO create procedure p2 as select 'second stored procedure' |
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:
|
1 2 |
USE Northwind GO |
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.
He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte
- PostgreSQL tutorial to create a user - November 12, 2023
- PostgreSQL Tutorial for beginners - April 6, 2023
- PSQL stored procedures overview and examples - February 14, 2023