GO

3

May 6, 2013 by Kenneth Fisher

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.

3 thoughts on “GO

  1. Robert Amann says:

    Thanks for making me more aware of GO. Already used this to load up a test database I needed.

  2. […] can cross batches. (If you don’t know what I mean by a batch you can read what I wrote about GO the batch separator.) It also means that when you close the session the temporary table is dropped. […]

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

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,646 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: