Using the GO keyword in SQL Server

Question

I know the use of the GO keyword. it sends several statements to the sql server as a whole group, instead of sending each statement one at a time. I hope that I am right!

but I want to know that programmers use it in real applications. for example, if we create a stored procedure, it also does the same, it also compiles the code and compiles a execution plan, and sends the entire group to the server.

Do we need to specify the GO keyword when encoding database objects, such as triggers, views, stored procedures?

Answer 1

GO is a command used to signal the end of a batch. Please note that this is not a T-SQL statement.

A package is a group of one or more Transact-SQL statements sent simultaneously from an application to SQL Server to execute

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

Area 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 result in an error: @ STRING2 is not declared. Since the scope of this variable ends with GO. Therefore, be careful with using GO and skill.

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

Answer 2

The GO keyword signals the completion of a package for SQL Server Management Studio - usually SQL Server Management Studio executes all statements in one package (the package can be considered as feedback to the database), but in some situations it may be necessary to execute statements in different batches (for example, the SET statement SHOWPLAN_ALL must be the only statement in the package)

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

 USE StackOverflow GO SELECT * FROM Comments 

It 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 not a T-SQL keyword; it is understood only by SQL Server Management Studio and other SQL tools. For example, the following work will not work and will throw an exception at runtime:

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

Answer 3

GO is not a keyword in SQL. This is a directive for SSMS and other tools for breaking a larger script into lots.

Answer 4

To add to what everyone else has stated, you cannot use GO in a stored procedure, trigger, view, UDF, or even dynamic SQL. It is intended for scripting only.

Answer 5

I suspect you would be better off using a semicolon (;) to separate statements.

Answer 6

There is no need to specify the GO directive on the SQL server.

Answer 7

MSDN Defines Go as

Tells the end of the Transact-SQL statement package to SQL Server utilities.

and further on:

GO is not a Transact-SQL statement; This is a team recognized by sqlcmd and the osql and SQL Server utilities Studio Management Code Editor.

SQL Server utilities interpret GO as a signal that they need the current batch of Transact-SQL statements in an instance of SQL Server. The current batch of statements consists of all statements entered since the last GO, or from the beginning of the session or script, if this is the first GO.

The most common time I use GO is to create object creation scripts

My creation 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 

Thus, two GOs are important because I want to make sure that I am on the correct DB and it will not work without GO. The second GO is vital, otherwise the stored procedure will try to run the grant statement as part of the procedure.