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.
Category: Keywords, Microsoft SQL Server, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL | Tags: microsoft sql server, SQL, sql statements, T-SQL
6 thoughts on “GO”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Thanks for making me more aware of GO. Already used this to load up a test database I needed.
No problem. I’ve always felt this was one of the most used yet least understood “commands”. Glad you liked it and thanks for the comment!
[…] 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. […]
[…] is a session. A batch is essentially an execution of code up to a batch separator, which is usually GO. In very simple terms the scope (how long they last) of a variable is at most the batch they were […]
[…] end of this it isn’t overly obvious to you that’s ok too. I do a lot of dynamic SQL and GO is one of my favorite […]
[…] Mistake 1) You can’t use GO in dynamic SQL. That would be because GO is a batch separator and not actually part of T-SQL. […]