The SQL Server GO keyword is extremely common when querying and developing SQL Server databases. It is important that you understand how it works if you regularly create objects (such as tables, stored procedures, views, etc.) in a SQL Server database.
In this very brief tutorial, we will discuss everything you need to know about the GO keyword and outline some important tips you need to remember.
We will discuss these topics:
- What is the GO keyword?
- A simple example of using the GO keyword
- A real-world example of using the GO keyword
- Tips and tricks
Let’s go:
1. What is the GO keyword?
The GO keyword separates T-SQL code into batches.
To really understand the SQL Server GO keyword, you really need to understand a thing-or-two about batches. Take a look at the following simple queries against a Products table:
SELECT * FROM Products WHERE ProductID = 26 SELECT * FROM Products WHERE Price > 100.00 SELECT * FROM Products WHERE ProductName = 'Coat Rack'
These three SELECT statements are all in single batch, which means all the code will be ran as a single unit.
(The reason I know it’s a single batch is because there is no GO keyword to separate the statements into multiple batches)
Before executing the code in this batch, SQL Server will parse the code in the batch and look for any errors. An error can be something like a syntax error or a reference to a table or other object that simply doesn’t exist.
After parsing the code, if no errors are discovered, SQL will go ahead and execute all the code in the batch.
However, if there is an error in the batch, none of the code in the batch gets executed. Again, this is because the code is ran as a single unit, meaning either everything runs or nothing runs.
Let’s look at an example in the next section.
2. A simple example of using the GO keyword
We’ll revisit the last example of a set of queries against the Products table:
We can see all three SELECT statements giving us three result sets.
Since there is no GO keyword anywhere in the code, it is all ran in a single batch. Since SQL Server did not encounter any errors when parsing all the code in this batch, it went ahead and ran all the code in the batch.
But let’s introduce a bit of chaos.
Let’s introduce a small typo into one of the queries, then execute all three queries in a single batch:
Since “Prc” is not the correct name of the column, we get an error message.
But if the middle query has the problem, why didn’t the other queries (those that don’t have any problems) run?
Answer: Because all this code is ran in a single batch, meaning either everything succeeds or everything fails!
We can introduce the GO keyword to split these statements into multiple batches:
We still get an error message for the query in the second batch, but the first and last batches return a success message of “1 row affected“. Also notice there is now a “Results” tab that was not there before. If we switch to the “Results” tab, we can see the result sets of the two successful queries:
Now, since we put each SELECT statement into it’s own batch, only the one SELECT statement fails to execute while the others execute just fine!
3. A real-world example of using the GO keyword
I want to discuss a problem that happened at work that inspired me to create this tutorial.
Let’s create a very simple stored procedure that increases the prices of all our products by ten percent:
Cool. After that procedure is created, I’ll go ahead an run another simple statement to give users in our Admins role permission to execute this procedure:
Alright, now I’ll go ahead and run these statements:
I’ll go ahead and execute this procedure to make sure it’s working:
Cool….
But there’s a problem
When I executed the code to create the stored procedure and add “Execute” permission to the Admins role, I did not separate those two statements into two separate batches. Therefore, both statements ran in a single batch.
In other words, the code that adds “Execute” permissions to the Admin role is part of the definition of the stored procedure!
Take a look at the definition:
Whoopsie daisies. Every time we run this procedure, we will be assigning “Execute” permissions to the Admins role.
Would that break anything? I mean, not really. It’s just completely unnecessary.
What we should have done was split those two statements into two batches using the SQL Server GO keyword.
I’ll go ahead an drop the procedure by running DROP PROCEDURE IncreasePricesTenPercent, then re-create it in it’s own batch, then run the permissions code in it’s own batch too:
Now if we look at the definition of the procedure, it only contains the code where we UPDATE the Products table, like it should:
Superb!
4. Tips and tricks
Here are some helpful tips and tricks you should know when working with the SQL Server GO keyword.
Tip # 1: Many “CREATE” statements must be by themselves in a batch
There are many CREATE statements that cannot include other CREATE statements in the same batch. They must be by themselves. The “CREATE PROCEDURE” statement is one of them. For example, this won’t work:
If we hover over the red squiggly line under the second stored procedure, it actually tells us that that statement must be in it’s own batch:
To fix this, we just need to add another GO after the first procedure:
Here is a list of all the statements of which there can be only one in the same batch:
- CREATE PROCEDURE
- CREATE FUNCTION
- CREATE VIEW
- CREATE SCHEMA
- CREATE TRIGGER
- CREATE DEFAULT
- CREATE RULE
Tip # 2: You can repeatedly run a batch by using “GO n“
You can run the code in a batch “n” number of times by saying “GO n“. For example, I’ll create a dummy table and insert 12 identical rows into it all at once by specifying “GO 12“:
This can be handy, especially if you’re trying to create dummy data like in my example.
Tip # 3: The SQL Server GO keyword is not actually part of T-SQL
Strangely, the GO keyword is not part of the T-SQL language. It is actually just a special keyword used for tools like SQL Server Management Studio to simply denote the end of a batch when writing code.
Next Steps:
Leave a comment if you found this tutorial helpful!
Here is another important tutorial you might find helpful:
SQL Server NULL: Are you making these 7 mistakes?
Also, we mentioned stored procedures several times in this tutorial. If you need to rundown on what a stored procedure is and how they can help us, definitely check out my full beginner friendly tutorial:
SQL Server Stored Procedure: The Ultimate Guide for Beginners
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, leave a comment. Or better yet, send me an email!
