What is the GO statement and why is it so important to use? When do I have to use it? When do I not use it? These are questions that have passed through my head from time to time while writing T-SQL within SQL Server.
First What Is It and When Should I Use It?
The GO statement lets SSMS (the interface) know when it’s the end of the batch. It basically defines the scope of what you are trying to send to the Database Engine. The below example sends two separate statements. The first statement changes the database context to run the next statement under, followed by the execution of the SELECT running against the database Demo. Simple, yes.
Example
USE DEMO GO SELECT * FROM MyTable GO
Gotcha’s
I’ve been caught out by this behavior in the past. Using GO in stored procedures can be tricky. There are times when you want to run a batch of statements together, but if you put a GO into the procedure and compile it you will notice that you lost any code that came after the GO. The GO signaled to that my ALTER or CREATE Procedure statement was done. It then ignored all the statement below it as part of the stored procedure.
Another Gotcha which can be both good and bad depending on your need. A Variable’s life span ends after each GO statement. If you declare a variable, run a statement to populate that variable and use that variable you can no longer use it once you send a GO.
Example
DECLARE @MyName VARCHAR(25) SELECT @MyName='Monica' PRINT @MYName GO PRINT @MyName + 'Again'
Cool things to do with GO
This is learned by chance just messing round. Did you know that if you put a number after GO it will run those statements that many times? This can be handy for generating a lot of load against a database for demos.
SELECT TOP (2) * FROM [AdventureWorks2014].[Person].[Address] GO 5
Don’t like the word go, change it. Yep you can change it to anything you want. Tool> Options> Query Execution
Change it to RUNNOW.
Let’s Try
DECLARE @MyName VARCHAR(25) SELECT @MyName='Monica' PRINT @MYName RUNNOW
HMMM Why didn’t that work… because I ran it in an existing Open Window (Session). Let’s try that again.
TADA! Much better.
DECLARE @MyName VARCHAR(25) SELECT @MyName='Monica' PRINT @MYName RUNNOW
Now that you know what it does, feel free to advance to GO and collect your $200. Enjoy.
