Did you know that the GO command isn’t really part of T-SQL? It’s what’s called a batch separator. And in case you were wondering that’s why you can’t put GO inside of stored procedures, functions etc.

So what does the “batch separator do? Well, it separates batches of course. Working from the top of the executed code down, every time you hit a GO command you start a new batch. So what exactly does that mean? Well, it’s as if you highlighted a section of code, executed that section, stopped, highlighted another section, executed that section, etc. Each of those sections is a “batch”.

Variables end their scope at the end of a batch.  Which is why variables have to be re-declared in each batch.  Temporary tables on the other hand are scoped at the connection level so they continue to exist through multiple batches.

I’ve done a couple of SQL Server Central QoTDs on the GO command. Here I demonstrate that the GO command can be configured in the SSMS options and in the Query options and here that there is a parameter that can be passed to the GO command. The discussions afterward made for some interesting reading too!

Changing the batch separator from GO to something else is moderately interesting trivia (at least to me) but probably only really useful if you get a hold of a script with a non standard (for MS SQL) batch separator. I was actually sent a piece of code from a vendor many years ago that used READY as the batch separator. Which, in the end, is how I learned about all of this.

The parameter however, does have some very useful applications. By now you may be wondering what parameter I could possibly be talking about. Well, if you pass in a positive integer n then the batch will be executed n times. So for example if I want to insert 5 lines into a table I can do this:

INSERT INTO tablename (firstname, lastname) VALUES ('John','Smith')
GO 5

If I want to load a million+ rows into a table say for testing, I can either write a loop, or do the following:

INSERT INTO TableName (fieldlist) VALUES (valuelist);
GO
INSERT INTO TableName
SELECT * FROM TableName;
GO 20

Essentially what will happen, is the first iteration will insert 1 additional row. The second iteration inserts 2 additional rows. The third iteration inserts 4 additional rows (total of 8 now). And you get a binary increase that very quickly becomes HUGE.

You could also use GO n to do some load testing. Set up several connections and run the following on each, using a different SP for each connection.

EXEC sp_Procedure1
GO 50

Now GO have a nice day.

Filed under: Keywords, Microsoft SQL Server, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL Tagged: microsoft sql server, SQL, sql statements, T-SQL