SQL Batch and GO - SQL Server for Oracle DBAs and Developers

In SQL Server, a batch is a group of Transact-SQL statements sent to SQL Server and compiled into a single execution plan. Then these statements are executed one at a time.

Last Update: Microsoft SQL Server 2012

SQL Statement Delimiter

Do not confuse GO command with a semicolon (;) that is the SQL statement delimiter in SQL Server (and Oracle as well).

Unlike Oracle, SQL Server allows you to omit the statement delimiter in SQL scripts (this feature will be removed in a future version of SQL Server ):

SQL Server:

   -- SQL statements without the statement delimiter allowed in SQL Server
   CREATE TABLE cities (name VARCHAR(90))
 
   INSERT INTO cities VALUES ('Boston')
   INSERT INTO cities VALUES ('Paris')

So when you see GO in SQL scripts, you may think this is the optional statement delimiter. But remember, GO command is not a statement delimiter:

SQL Server:

   -- Two SQL batches
   CREATE TABLE cities (name VARCHAR(90))
   GO
 
   INSERT INTO cities VALUES ('Boston')
   INSERT INTO cities VALUES ('Paris')
   GO

GO is Batch Separator

GO is even not a Transact-SQL statement, it is a command recognized by SQLCMD (analog of Oracle SQL*Plus) and SQL Server Management Studio Query tool that signals the end of the current SQL batch.

When a GO command occurs, all statements entered since the last GO (or from the beginning of the script if this is the first GO) are sent to SQL Server as a single batch.

A line with a GO command cannot contain any other statements , however it can contain comments.

Note that GO itself is never sent to SQL Server, and ADO.NET, JDBC, ODBC etc. applications must not contain GO command when they send a batch, otherwise they receive a syntax error .

Using GO to Repeat Batch Execution Multiple Times

GO command allows you to repeat a batch multiple times. Specify an integer number after GO command:

SQL Server:

  DELETE FROM cities;
  GO
 
  -- Repeat batch of 2 INSERT statements 100 times
  INSERT INTO cities VALUES ('Boston')
  INSERT INTO cities VALUES ('Paris')
  GO 100
 
  SELECT COUNT(*) FROM cities;
  # 200

200 rows were inserted in the batch above.

Error Handling in SQL Batches

Error handling in SQL batches depends on the type of error.

Syntax Error

Since a SQL batch is sent to SQL Server as a single compilation unit, a syntax error prevents the execution of any statement in the batch containing the error.

SQL Server:

  PRINT 'Batch1'
  GO
 
  -- Batch 2 contains a syntax error
  PRINT 'Batch2 - Start'
  PRIN 'Batch2 - Middle'
  PRINT 'Batch2 - Start'
  GO
 
  PRINT 'Batch3'
  GO
 
  #Batch1
  #Msg 102, Level 15, State 1, Line 4
  #Incorrect syntax near 'PRIN'.
  #Batch3

You can see that the second batch contains a syntax error and none of its statements were executed.

At the same time, the first and third batches were executed successfully, despite the SQL script contains a syntax error in the second batch.

Run-time Error in DDL Terminates Batch

If during execution of a batch, you get a run-time error in a DDL statement (CREATE TABLE i.e), the remaining statements in the batch are not executed:

SQL Server:

  IF OBJECT_ID('cities') IS NOT NULL
    DROP TABLE cities;
  GO
 
  CREATE TABLE cities (name VARCHAR(90) PRIMARY KEY)
  PRINT 'Table created 1'
 
  -- Table already exists (run-time error will be raised)
  CREATE TABLE cities (name VARCHAR(90) PRIMARY KEY)
  PRINT 'Table created 2'
 
  GO
 
  # Table created 1
  # Msg 2714, Level 16, State 6, Line 5
  # There is already an object named 'cities' in the database.

You can see that the second PRINT statement was not executed after the run-time error in the second CREATE TABLE statement.

Constraint Violation Run-time Error Terminates the Current Statement Only

A constraint violation error (duplicate key, CHECK constraint violation i.e.) stops the execution of the current statement only, all the remaining statements in the batch are executed:

SQL Server:

  INSERT INTO cities VALUES ('Boston')
  PRINT 'After 1st insert'
 
  -- Duplicate key error will be raised
  INSERT INTO cities VALUES ('Boston')
  PRINT 'After 2nd insert'
  GO
 
  # After 1st insert
  # Msg 2627, Level 14, State 1, Line 8
  # Violation of PRIMARY KEY constraint 'PK__cities__72E12F1AF8B24C99'. 
  # Cannot insert duplicate key in object 'dbo.cities'. The duplicate key value is (Boston).
  # The statement has been terminated.
  # After 2nd insert

You can see that although the second INSERT statement caused the key violation error, the batch continued and the second PRINT was executed.

SQL Batch Restrictions

There are some restrictions when you use SQL batches.

Single CREATE Statement in Batch

There can be multiple CREATE TABLE and CREATE INDEX statements in a single batch, but a CREATE VIEW, CREATE FUNCTION, CREATE PROCEDURE and CREATE TRIGGER statement cannot be combined with other statements in a batch.

SQL Server:

  -- Multiple CREATE TABLE can be in a batch
  CREATE TABLE cities1 (name VARCHAR(90));
  CREATE TABLE cities2 (name VARCHAR(90));
  GO
 
  -- Only one CREATE VIEW can be in a batch
  CREATE VIEW cities_view1 AS SELECT * FROM cities1;
  CREATE VIEW cities_view2 AS SELECT * FROM cities2;
  GO  
 
  # Msg 156, Level 15, State 1, Procedure cities_view1, Line 2
  # Incorrect syntax near the keyword 'CREATE'.

Local Variables Does Not Span Multiple Batches

A local variable declared in a batch is not available in other batches:

SQL Server:

  -- Declare and print a variable in the current batch  
  DECLARE @message VARCHAR(10) = 'Hello';
  PRINT @message;
 
  -- Terminate the batch
  GO
 
  -- Variable does not exist anymore
  PRINT @message;
 
  # Hello
  # Msg 137, Level 15, State 2, Line 3
  # Must declare the scalar variable "@message".

EXECUTE Keyword is Required to Execute Stored Procedures

In a batch you have to use EXECUTE (or EXEC) keyword to execute a stored procedure if it is not the first statement in the batch:

SQL Server:

  -- Trying to execute 2 stored procedures
  sp_helpdb 'master'
  sp_who
  GO
  # Msg 102, Level 15, State 1, Line 3
  # Incorrect syntax near 'sp_who'.

If you add EXEC keyword before the second procedure, the batch is executed successfully:

SQL Server:

  -- Trying to execute 2 stored procedures
  sp_helpdb 'master'
  EXEC sp_who
  GO
  # Query executed successfully

Resources

SQLines Services

SQLines offers services to migrate Oracle databases and applications to Microsoft SQL Server. For more information, please Contact Us.

You could leave a comment if you were logged in.