PASS GO 500

And you thought that you only got $200 for passing GO, didn't you?  Well, this isn't Monopoly, my friends, this is SQL Server.  And while attending the PASS Community Summit this year, I saw Joe Webb do this trick.  I wasn't the only one in the crowd who went, "Wha???" when they saw it.

Most of you readers already know that GO is not a SQL command, but rather it is the commonly used word to separate batches.  In other words, the client tool (Query Analyzer, SQL Management Studio, OSQL.exe, etc.) parse whatever commands you execute, separating them into batches whenever GO is found.  (Actually, GO is not a magic word by itself, you can set the "batch separator word" to be whatever you want in the Options panel of Query Analyzer and Management Studio.)

Well, it turns out that in SQL 2005, a new option was implemented whereby you can put a number after the GO command and that will cause the batch to be executed that many times.  This does not work in SQL 2000 and earlier.  Here's an example if you want to test it out for yourself.

-- Create a test table to use in our test. CREATE TABLE dbo.TestGo (
RecID INT NOT NULL IDENTITY(1,1),
SomeText VARCHAR(50) NULL
) GO

-- Now a simple test to prove that this next batch is being executed multiple times.
INSERT INTO dbo.TestGo VALUES ('This is a test.') 
GO 500

-- Look at the records to prove it worked
SELECT * 
FROM dbo.TestGo
-- Now let's cleanup after ourselves.
DROP TABLE dbo.TestGo 
GO

So there you have it.  The batch that precedes the GO with a number is executed however many times you specify.  I cannot say at this point that I would recommend using that for anything other than ad-hoc testing, perhaps to simulate a load; or possibly to generate some quick test data.  But I thought it was a neat trick and after proving to myself that it really worked, thought I would share it.  And for those who are curious, yes, this is documented in Books On-Line.

UPDATED: Added formatting to code blocks.

posted @ Saturday, November 22, 2008 10:22 PM

Print

Comments on this entry:

# re: PASS GO 500

Left by Roy Harvey at 12/5/2008 4:52 AM
Gravatar
Kalen Delaney blogged about this one a while back.
http://sqlblog.com/blogs/kalen_delaney/archive/2007/07/22/did-you-know-run-a-batch-multiple-times.aspx

You know, of course, that GO is processed by the FRONT END (Management Studio, Query Analyzer, ISQL, OSQL, SQLCMD).

With SQLCMD you have the option of using a scripting variable, thus making it somewhat dynamic. HOWEVER, where we have variables we also have the need to consider unexpected data. What happens when the value is ZERO? Or NEGATIVE? Using OSQL a value of zero gave the same results as a value of 1, but a negative number gave an error. Using SQLCMD a value of zero gave zero repetitions, as did a negative number.
Comments have been closed on this topic.
«July»
SunMonTueWedThuFriSat
293012345
6789101112
13141516171819
20212223242526
272829303112
3456789