Caveman's Blog

My commitment to learning.

SQL Server: GO command

leave a comment »


GO is a command that is a batch terminator. A “batch” is a set of Transact-SQL statements that get sent to an instance of the SQL Server in one network packet. Basically we can use GO to signal the end of a batch. Transact-SQL statements of a batch are compiled as a single execution plan.

Following is an example of dropping and recreating the Employee table in the Demo database. The GO command in this example generates three batches of SQL statement. The first batch sets the context database, while the second batch is used to drop an existing Employee table. The third batch is used to create the employee table.


USE [Demo]
GO

/****** Object:  Table [dbo].[Employee]    Script Date: 03/23/2012 00:22:26 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]') AND type in (N'U'))
DROP TABLE [dbo].[Employee]
GO

CREATE TABLE [dbo].[Employee](
[EmpID] [int] IDENTITY(1,1) NOT NULL,
[EmpName] [varchar](50) NOT NULL,
)
GO

Following are some rules around the usage of GO command:

  1. A Transact-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments.
  2. Any execution of a stored procedure after the first statement in a batch must include the EXECUTE keyword.
  3. The scope of local (user-defined) variables is limited to a batch, and cannot be referenced after a GO command.

GO command can also accept one optional  integer as an argument. In this case all the statements preceding the GO with an integer argument will execute as many times as specified by the argument. The following example generates two random unique identifiers

--SYNTAX: GO [count]

select newid()
GO 2

OUTPUT

References:
1. MSDN Online

Advertisements

Written by cavemansblog

March 23, 2012 at 12:47 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: