Watch this week's video on YouTube
When I started working with T-SQL, I thought the GO command was optional, kind of like semicolons. It appeared in plenty of SSMS generated scripts, but it seemed like I never had to add it to any queries of my own:
SELECT 1 as Col1 INTO #Test1;
GO
-- ...or....
SELECT 1 as Col1 INTO #Test2;
-- both seem to work equally well!
Turns out that GO isn't T-SQL at all, but a command that allows apps (like SSMS) to send batches of queries to SQL Server. It also turns out it has some more functionality than I originally thought.
So when should you use GO?
Using GO When Required
Just like semicolons not being entirely optional (ever try to run a CTE after a statement without a semicolon?), GO isn't completely optional either.
Some operations require that GO appear immediately after them:
DROP PROCEDURE IF EXISTS dbo.BatchTest;
CREATE PROCEDURE dbo.BatchTest
AS
BEGIN
SELECT 1 as Col1
END;
The above script will fail with the error message "'CREATE/ALTER PROCEDURE' must be the first statement in a query batch."
There are multiple commands ("CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, and CREATE VIEW") that require being the first statement of a batch, so using GO is required if you are going to try running other statements as part of your script.
So how do you run that CREATE PROCEDURE statement after first checking and dropping that procedure? Just add GO so that CREATE PROCEDURE is the first statement of the batch:
DROP PROCEDURE IF EXISTS dbo.BatchTest;
GO
CREATE PROCEDURE dbo.BatchTest
AS
BEGIN
SELECT 1 as Col1
END;
Executing Commands Multiple Times
So the previous example was one where SQL Server required me to type two extra characters to run certain commands. Boo. What about something actually useful I can do with GO?
Sometimes you may want to run a statement more than once. You can do that by being trigger happy with the F5 key or your mouse button, but you don't want to do that 10,000 times, do you?
That's where GO shines. Simply add an integer after GO and SQL Server will execute that batch of statements however many times you specified. For example, the following code will insert 10,000 rows into a table:
INSERT INTO dbo.TestData VALUES (1,2,3)
GO 10000
This is really useful when creating test data or simulating workloads.