Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

GO

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

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...