GO n | ITPro Today: IT News, How-Tos, Trends, Case Studies, Career Tips, More

As you probably know, GO is not a T-SQL command rather a command
used by client tools like SQL Server Management Studio, SQLCMD and
OSQL to indicate the end of a batch. Apparently, the GO client command
was enhanced allowing you to specify the number of times to invoke the
batch. For example, run the following in SSMS:

PRINT 'Hello';
GO 5

And the batch will be invoked 5 times. You will get the following output:

Beginning execution loop
Hello
Hello
Hello
Hello
Hello
Batch execution completed 5 times.

I couldn’t find this option documented in Books Online or any other official
documentation. I learned about it first from code written by Kalen Delaney,
and initially I thought it was just a typo.

Note that since GO is a client command and not a T-SQL one, you can use
it with Microsoft SQL Server 2005’s client tools even when connected to
Microsoft SQL Server 2000 instances.

I’ll show here a couple of other examples for using this option. The following
code creates a table where all columns have default values, and uses GO 10
to insert 10 rows with the DEFAULT VALUES option:

-- Create and populate table T1
SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
CREATE TABLE dbo.T1
(
  id INT NOT NULL IDENTITY PRIMARY KEY,
  dt DATETIME NOT NULL DEFAULT(GETDATE()),
  loginname SYSNAME NOT NULL DEFAULT(SUSER_SNAME()),
  guidcol UNIQUEIDENTIFIER NOT NULL DEFAULT(NEWSEQUENTIALID()),
  rnd INT NOT NULL DEFAULT(ABS(CHECKSUM(NEWID()))%100+1)
);
GO
INSERT INTO dbo.T1 DEFAULT VALUES;
GO 10
SELECT * FROM dbo.T1;
GO

Here’s the output I got from this code:

Beginning execution loop
Batch execution completed 100 times.
id          dt                      loginname      guidcol                              rnd
----------- ----------------------- -------------- ------------------------------------ ----
1           2008-02-20 15:34:12.180 Domain1\User1  203D7D48-C9DF-DC11-9C7E-00A0D171D8BE 65
2           2008-02-20 15:34:12.180 Domain1\User1  213D7D48-C9DF-DC11-9C7E-00A0D171D8BE 89
3           2008-02-20 15:34:12.180 Domain1\User1  223D7D48-C9DF-DC11-9C7E-00A0D171D8BE 83
4           2008-02-20 15:34:12.180 Domain1\User1  233D7D48-C9DF-DC11-9C7E-00A0D171D8BE 46
5           2008-02-20 15:34:12.180 Domain1\User1  243D7D48-C9DF-DC11-9C7E-00A0D171D8BE 83
6           2008-02-20 15:34:12.180 Domain1\User1  253D7D48-C9DF-DC11-9C7E-00A0D171D8BE 76
7           2008-02-20 15:34:12.180 Domain1\User1  263D7D48-C9DF-DC11-9C7E-00A0D171D8BE 91
8           2008-02-20 15:34:12.180 Domain1\User1  273D7D48-C9DF-DC11-9C7E-00A0D171D8BE 57
9           2008-02-20 15:34:12.180 Domain1\User1  283D7D48-C9DF-DC11-9C7E-00A0D171D8BE 53
10          2008-02-20 15:34:12.180 Domain1\User1  293D7D48-C9DF-DC11-9C7E-00A0D171D8BE 66

As another example, the following code creates an auxiliary table called
Nums and populates it with 1,048,576 sequential integers. It does so by first
inserting a single row with the value 1, then running 20 rounds of a batch that
doubles the number of rows in the table every time:

-- Create and populate Nums table with 1,048,576 rows
SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID('dbo.Nums', 'U') IS NOT NULL DROP TABLE dbo.Nums;
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
INSERT INTO dbo.Nums(n) VALUES(1);
GO
INSERT INTO dbo.Nums(n)
  SELECT n + (SELECT MAX(n) FROM dbo.Nums) FROM dbo.Nums;
GO 20

All in all, I have to say that I feel a bit uncomfortable with this feature for
two main reasons:
1. The code is not very structured. It’s not really apparent in the code to
people that are not familiar with this feature what it does. It reminds me of
problematic constructs like GOTO.
2. You don’t get a loop within the batch; rather you get multiple batches that
are submitted to the SQL Server instance.

So my general recommendation would be to use it when you need
something fast and dirty, for your own test purposes. Otherwise, you can
always add a little bit more code and form a simple loop within the batch
using T-SQL.

Cheers,
--
BG

Comments

Plain text