For years, Microsoft SQL Server developers and administrators have been familiar with a two letter verb – GO. “GO” is used as a batch separator, and we have never even thought of customizing the batch separator to be something other than “GO”. Here’s what Books On Line has to say about “GO”:

“GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.“

Recently, Madhivanan ( blog ) posted an interesting tip on the recently launched “Just Learned” page on BeyondRelational.com. He was talking about how the batch separator (“GO”) can be used as a loop – http://beyondrelational.com/justlearned/posts/75/go-command-is-also-a-while-loop-from-version-2005-onwards.aspx. Jacob mentioned that the batch separator can be customized, which got us (Madhivanan, Jacob & I) into an interesting discussion. Here’s the trail of thought that followed:

Customizing the Batch separator in SSMS

Here are a set of very simple, easy steps to customize the batch separator in SSMS:

1. Launch SSMS
2. Go to Tools –> Options
3. Click on the “Query Execution” node
4. Notice that we have an option to change the Batch Separator
5. Change the batch separator
6. Click “OK”

I changed the “GO” with “RUN”, and here’s the impact – it’s fun, isn’t it?

USE AdventureWorks2008R2
GO
USE AdventureWorks2008R2
RUN

Here’s what you would get if you run the first 2 lines above.

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near ‘GO’.

Surprised? Don’t be – if you run the next 2 lines, you will see that our change has taken effect:

Command(s) completed successfully.

The good and the bad…

Customizing the batch separator is fun, but we soon encounter the dark side of the coin. Because SSMS is a client-side utility, these changes are restricted to you for the SSMS on your machine only. What this means is that if you share your SQL file with the custom batch separator to a colleague, they will encounter errors.

What this also means is that other SQL Server utilities like SQLCMD are unaware of this change!

Connect to your SQL Server instance via SQLCMD, and run the batch from above. Here’s what you would get:

Summarizing, customizing the batch separator is more a means of having some fun rather than some practical use.

Using a custom batch separator in SQLCMD

Let’s assume for a while that you want to continue using a customized batch separator, and use SQLCMD a lot. The best option is to explore the various options available with SQLCMD:

Microsoft (R) SQL Server Command Line Tool

Version 10.0.4000.0 NT INTEL X86

Copyright (c) Microsoft Corporation.  All rights reserved.

usage: Sqlcmd            [-U login id]          [-P password]

  [-S server]            [-H hostname]          [-E trusted connection]

  [-d use database name] [-l login timeout]     [-t query timeout]

  [-h headers]           [-s colseparator]      [-w screen width]

  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]

  [-c cmdend]            [-L[c] list servers[clean output]]

  [-q "cmdline query"]   [-Q "cmdline query" and exit]

  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]

  [-u unicode output]    [-r[0|1] msgs to stderr]

  [-i inputfile]         [-o outputfile]        [-z new password]

  [-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit]

  [-k[1|2] remove[replace] control characters]

  [-y variable length type display width]

  [-Y fixed length type display width]

  [-p[1] print statistics[colon format]]

  [-R use client regional setting]

  [-b On error batch abort]

  [-v var = "value"…]  [-A dedicated admin connection]

  [-X[1] disable commands, startup script, enviroment variables [and exit]]

  [-x disable variable substitution]

  [-? show syntax summary]

I have high-lighted a particular parameter –c, which per Books On Line:

“-c cmd_end

Specifies the batch terminator. By default, commands are terminated and sent to SQL Server by typing the word "GO" on a line by itself. When you reset the batch terminator, do not use Transact-SQL reserved keywords or characters that have special meaning to the operating system, even if they are preceded by a backslash.”

Let’s try to use the –c parameter and supply our own batch separator:

The big question – If you had to, what would you propose as a replacement of “GO”?

I hope that you liked the discussion above, and a chain of thought has also started in your mind. What would you propose as a replacement of “GO”? I request you to post your thoughts in continuation to our original discussion at: http://beyondrelational.com/justlearned/posts/75/go-command-is-also-a-while-loop-from-version-2005-onwards.aspx as replies to this post.

Until we meet next time,

Be courteous. Drive responsibly.