Why “GO” statement is important in SQL Server? Do we really need it?

Share this blog:
  •  
  •  
  •  
  •  
  •  

YES. Very much needed!

GO is a batch separator in SQL. Go is essentially is SSMS code rather than a regular T-SQL code.

Batch is a set of SQL statements sent together to SQL Engine.

It is used to commit a set of queries or a batch to database. It creates a stop in the transaction, when SSMS encounters a GO. It executes/commits all queries till that line and starts over a new batch.

It can be used to end the scope of a local variable in SSMS.

I won’t say this is the only reason for using GO. If you have a create procedure statement and insert statements together in a deployment script, then you need to separate them using GO. Otherwise you will get error that Create Procedure needs to be first statement in the batch.

So, we must specify GO there.

GO doesn’t allow any other statement in the same line; except an integer.

GO followed by an integer allow SQL optimizer to execute that statement by defined (integer) number of times. We will see in the following example.

Example 1: GO as a Batch separator 
PRINT 'Batch1'
 --GO 
 -- Batch 2 contains a syntax error here
PRIN 'Batch2'
--GO 
PRINT 'Batch3'
--GO
OUTPUT: 
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'PRIN'.

Once I uncomment GO
PRINT 'Batch1'
 GO 
-- Batch 2 contains a syntax error here
PRIN 'Batch2'
--GO 
PRINT 'Batch3'
GO
OUTPUT: 
Batch1
Msg 2812, Level 16, State 62, Line 3
Could not find stored procedure 'PRIN'.
Batch3

Batch1 and Batch2 printed successfully.
Example 2: GO as a scope terminator 
  -- Declare and print a variable in the current batch  
  DECLARE @Msg VARCHAR(50) = 'Hello World!';
  PRINT @Msg;
 
  -- Terminate the batch
  GO 
  -- Variable does not exist anymore
  PRINT @Msg;

OUTPUT:
Hello World!
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@Msg".

Example 3: GO to execute statement in number of times
PRINT 'Hello'
GO 5

OUTPUT:
Beginning execution loop
Hello
Hello
Hello
Hello
Hello
Batch execution completed 5 times.

Example 4: GO doesn’t accommodate any statement with it, exception is above
PRINT 'Hello'
GO Select 1

OUTPUT:
A fatal scripting error occurred.
Incorrect syntax was encountered while parsing GO.
Example 5: GO statement can’t be part of the definition of the Stored Procedure, Function, View etc.
CREATE PROCEDURE GOStatementTest
AS
BEGIN
    SELECT 'Ravish'
    GO  
    SELECT 'Ranjan'
END

OUTPUT:
Msg 102, Level 15, State 1, Procedure GOStatementTest, Line 5
Incorrect syntax near 'Ravish'.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'END'.

GO is not a hard-fixed keyword, you can replace it with your own choice.

How you can change GO?

GO can be changed to whatever you want:

Tool -> Options

Now click on the options and select Query Execution Tab.

In place of GO you can put in your own choice name.

5 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

3 + four =