Systems Engineering and RDBMS

The GO command in SQL Server

Posted by decipherinfosys on October 11, 2007

The GO command in SQL Server is not a T-SQL command.  It is a command that is recognized by the SQL Server utilities like osql or sqlcmd or the editors – Query Analyzer or SSMS.  This just signals these utilities/editors to batch up the data and send it to the server.  The batch comprises of all the statements entered since the last GO statement.

So, if you are playing with your scripts in the editors and are using local variables, remember to put the GO command only at the right place else the local variables will go out of the scope as soon as the batch is over, example:

declare @test int
set @test = 1
go

select @test

will give an error:

Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable “@test”.

Another little improvement that was made in version 2005 was that you can assign a number to make the same batch execute multiple times – this helps when trying to create some volume data using scripts that change data in their select list using functions.  Here is a small example to illustrate that:

SET NOCOUNT ON

CREATE TABLE #TEST_TBL (COL1 INT IDENTITY, COL2 UNIQUEIDENTIFIER)
GO

INSERT INTO #TEST_TBL (COL2) VALUES (NEWID())
GO 10

SELECT * FROM #TEST_TBL

And the output:

Beginning execution loop
Batch execution completed 10 times.

COL1        COL2
----------- ------------------------------------
1           7BB18173-EA7B-4D2A-A9AF-6A186FF278E0
2           98DA79BA-CE6F-463E-8759-5222B8FB2E02
3           BBF1DCFC-A25C-4369-BF06-80172D72CB51
4           785633B4-3A70-4854-8D76-B00515844B90
5           97E13CDA-4C32-457C-8573-6293F4D93426
6           191F9DC1-722D-4F17-A0EE-3BAF48E89279
7           FDC3A1DF-B8B9-4660-B4B8-B33B10F114B4
8           7B3A0126-B93D-456A-B816-C40C6C2811CD
9           0E4A1C57-2C37-44C1-B124-19920EB1D790
10          AF55693C-899A-44CC-A2D4-293C2B127562

Sorry, the comment form is closed at this time.

 
%d bloggers like this: