Shortcut: Using a Count with the GO Batch Separator in T-SQL

In T-SQL, a script is a set of one or more batches.

For example, if we have the following script and click Execute, it looks like all the commands were sent to the server and executed all at once:

But that isn't what happened.

What did happen is that SQL Server Management Studio (SSMS) found the word GO and broke the script into a series of batches. In this case, there were three batches. First, it sent the commands shown here as Batch 1 to the server, waited for them to execute, then sent Batch 2, waited for it to execute, then sent Batch 3, and waited for it to execute.

It looks like it just happened all at once, but it didn't.

The key thing to understand about GO is that it's not a T-SQL command. It's a batch separator. We use GO to separate batches of commands that are part of a single script. The word GO is never sent to SQL Server. It only has meaning to the client tool (ie in this case SSMS).

In fact, you can change it. One of my friends that's a Star Trek fan has all his scripts with ENGAGE rather than GO.

You can see that I've changed it in Tools -> Options :

And now if I open a new query window, that works just fine:

I can't recommend doing that as your scripts won't be much use to anyone else.

So keep in mind that it's the client that understands GO, not the server. And that leads us to our shortcut of the day.

You can add a count after the word GO, and then SSMS will send your commands from that batch to the server more than once:

Notice that Intellisense in SSMS doesn't understand the syntax (there is a red squiggle) but it works just fine.

I find this shortcut quite useful if I want to execute a command a substantial number of times. An example is when I want to insert a bunch of default rows into a table; another is for executing commands while testing.






Leave a Reply

Your email address will not be published. Required fields are marked *