I can see the confused looks. “Joel what are you talking about?”

We’re talking about Batches

By default, in SQL Server Management Studio (SSMS), your query is submitted and executed in a batch. The batch separates the work you want SQL to perform.

For example if you try to run the following code

You will receive the following error

However, if you separate them into two batches SQL will execute successfully since both are submitted and executed separately.

Changing the Batch Separator

The word “GO” is the default batch separator for SQL. Most drivers and tools recognize this.

Interestingly, SQL Server Management Studio (SSMS) will allow you to change the batch separator. Under Tools > Options you can select to change the batch separator for SQL Server Query Execution.

Note: Changing this setting will only affect new query windows and not those already opened.

Why would you do this?

Good Question!

Other than playing a cruel April Fool’s joke on someone, or perhaps teaching someone to lock their pc when they step away I have never really found a use for this option… until now.

For a great party prank change the batch separator from “GO” to “SELECT” on a friends PC and share those funny memories forever. As an aside – please go to better parties.

An Actual Use Case!

I have no idea why this feature was added, but it did solve a problem for me. Quite frequently I will script data from a table.  The downside is that I typically want to use that data in a stored procedure or deployment process for a test environment and it can be a real pain to remove all of those “GO” statements.

Since “GO” is a fairly common letter combination you do not just want to do a find/replace on that to remove them.

Instead, before scripting out the data change your Batch separator to “~GO~”. Now when you script out  your data you will get something that is much easier to find and replace with confidence.

Do you have any tricks you use? Do you have a crazy party prank story?

Let us know in the comments below.