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 ?
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
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();
}
GO is not a Keyword in SQL. It's a directive for SSMS and other tools to break a larger script into batches.
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.
I suspect you're going to be better off using semicolon (;) to separate statements.
No there is no need to specify the GO directive in SQL server.
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.
