Have you ever come across the GO statement while writing T-SQL within SQL Server and wondered what it is and when to use it? In this article, we will explore the GO statement and its significance in SQL Server.

The GO statement is used to signal the end of a batch of statements in SQL Server Management Studio (SSMS). It defines the scope of the statements that you are sending to the Database Engine. Let’s take a look at an example:

USE DEMO
GO
SELECT * FROM MyTable
GO

In the above example, the first statement changes the database context to “DEMO” and the second statement executes a SELECT query against the “MyTable” table. The GO statement separates these two statements, indicating that they should be treated as separate batches.

However, there are a few things to be cautious about when using the GO statement. If you include a GO statement within a stored procedure and compile it, any code that comes after the GO statement will be ignored. This means that the GO statement effectively marks the end of the stored procedure.

Another thing to note is that the lifespan of a variable ends after each GO statement. If you declare a variable, populate it with a value, and use it in a statement, you won’t be able to use that variable again after a GO statement.

Here’s an example:

DECLARE @MyName VARCHAR(25)
SELECT @MyName = 'Monica'
PRINT @MyName
GO
PRINT @MyName + 'Again'

In the above example, the variable @MyName is declared, assigned a value, and printed. However, after the GO statement, the variable @MyName cannot be accessed or used.

Now, let’s explore some cool things you can do with the GO statement. Did you know that you can specify a number after the GO statement to run the batch of statements multiple times? This can be useful for generating a lot of load against a database for demos or testing purposes.

SELECT TOP (2) *
FROM [AdventureWorks2014].[Person].[Address]
GO 5

In the above example, the SELECT statement will be executed 5 times, generating the result set multiple times.

Lastly, if you don’t like using the word “GO,” you can actually change it to anything you want. In SSMS, go to Tools > Options > Query Execution and modify the “Batch separator” setting. For example, you can change it to “RUNNOW.”

However, please note that changing the batch separator only works for new query windows or sessions. If you try to use the new batch separator in an existing open window, it won’t work until you open a new window or session.

Now that you have a better understanding of the GO statement in SQL Server, feel free to use it confidently in your T-SQL scripts. Just remember its purpose of separating batches and be aware of its implications on stored procedures and variable lifespans.

Happy coding!