using GO keyword in SQL Server

Question

i know the use of GO keyword. it sends multiple statements to sql server as a whole group, instead of sending each statement one by one. i hope i am right!

but i want to know that do programmers use it in real applications. like if we create a stored procedure, then that also does the same thing,it also compiles the code and make an execution plan , and send the whole group to the server.

so do we need to specify the GO keyword in the coding of database objects such as triggers, views, stored procedures ?

Answer 1

The GO keyword signals the end of a batch to SQL Server Management Studio - normally SQL Server Management Studio executes all statements in a single batch, (a batch can be thought of as a round trip to the database), however in certain situations it may be desired to execute statements in different batches (for example the SET SHOWPLAN_ALL statement must be the only statement in a batch)

For example, executing the following script in SQL Server Management Studio:

USE StackOverflow
GO
SELECT * FROM Comments

Is roughly equivalent to doing the following in C#:

using (var cmd = new SqlCommand("USE StackOverflow", conn))
{
    cmd.ExecuteReader();
}
using (var cmd = new SqlCommand("SELECT * FROM Comments", conn))
{
    cmd.ExecuteReader();
}    

Note that GO is not a T-SQL keyword, it is only understood by SQL Server Management Studio and other SQL tools. For example the following wont work and will result in a runtime exception:

string cmdText = @"
USE StackOverflow
GO
SELECT * FROM Comments";

using (var cmd = new SqlCommand(cmdText, conn))
{
    cmd.ExecuteReader();
}

Answer 2

No there is no need to specify the GO directive in SQL server.

Answer 3

GO is a command used for signaling the end of a batch. Note that it is not a T-SQL statement.

Batch is a group of one or more Transact-SQL statements sent at the same time from an application to SQL Server for execution

GO is very useful in SQL Server. Though, you need to use it only when is really needed. So, you need to keep in mind that along with defining a batch using the GO command, you define the scope of that specific piece of T-SQL code. The local variables defined in a batch are specific to that batch.

Scope example -

DECLARE @STRING1 AS VARCHAR(50)
DECLARE @STRING2 AS VARCHAR(50)

SET @STRING1='BATCH 2'
SET @STRING2='BATCH 3'

SELECT @STRING1 AS RESULT
GO

SELECT @STRING2 AS RESULT
GO

Running this will give you error saying @STRING2 is not declared. Because the scope of that variable ends with the GO. So beware of using GO and use smartly.

Source - http://aartemiou.blogspot.com/2009/08/using-go-command-in-sql-server.html

Answer 4

GO is not a Keyword in SQL. It's a directive for SSMS and other tools to break a larger script into batches.

Answer 5

To add to what everyone else has stated... you can't actually use GO in a Stored Procedure, Trigger, View, UDF, or even in Dynamic SQL. It is for use with scripting only.

Answer 6

MSDN Defines Go as

Signals the end of a batch of Transact-SQL statements to the SQL Server utilities.

and goes on further to say

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.

The most common time I use GO is for Object creation Scripts

My create scripts usually follow this pattern

USE SomeDatabase
GO

If Exists(SELECT * FROM ...)
 DROP

CREATE PROC as foo
BEGIN
END
GO 

Grant exec on Foo to Bar

So the two GOs are important because I want to make sure I'm on the right DB and it won't work without the GO. The second GO is vital otherwise the stored procedure will try to run the grant statement as part of the procedure.