SSMS (and other SQL Server utilities) utilize a batch separator to, well, separate batches of T-SQL statements. Before we go jump into batch separators, let’s first talk about what a batch is.

Batch

Microsoft explains exactly what a batch is at this link:

A batch is a group of one or more Transact-SQL statements sent at the same time from an application to SQL Server for execution.

This link also has a few rules pertaining to batches:

• CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch. The CREATE statement must start the batch. All other statements that follow in that batch will be interpreted as part of the definition of the first CREATE statement.
• A table cannot be changed and then the new columns referenced in the same batch.
• If an EXECUTE statement is the first statement in a batch, the EXECUTE keyword is not required. The EXECUTE keyword is required if the EXECUTE statement is not the first statement in the batch.

Batch Separator

That first rule above is what causes us to need a batch separator. What this rule doesn’t state is that this applies to the ALTER format of these statements also.

Frequently, a script (especially an involved change script) will contain many T-SQL based objects which are created by running all of these as one file. However, they need to be in separate batches. So, these utilities implement a batch separator. The batch separator is NOT a T-SQL command; it is a SQL client command. You are probably used to seeing this implemented as “GO”. The complete documentation for this is at this link.

Since this is a SQL client command (and not a T-SQL command), the clients have some leeway with how to work with this. These utilities allow you to set what you want to use for the batch separator. In SSMS, this is performed in the options screen:

If you’re a fan of Captain Picard, you know that he would cause his orders to be executed with the command “Make It So”.

We can make SSMS behave this way also. Let’s change the batch separator to “MakeItSo”:

Now when we open up a new query window, we can use that to separate batches. In the following screen shot, I declare the same variable in two different batches. If these were in the same batch, this would generate an error. Also note that Intellisense is not warning about the unknown word.

When the statements in the query window are run, both print statements are executed without errors.

Pseudo-cursor

If you look at the syntax for the batch separator, there is an optional count argument to this command. This will cause the preceding batch to be executed the specified number of times. To see this in action, let’s add “MakeItSo 5” to the end of the last batch above:

We can see that the print statement was executed 5 times. Additionally, the messages show the start and stop of the loop.

Other clients

Other client utilities (sqlcmd, osql) allow you to change the batch separator with the “-c” option. It appears that this only works when using an input file. When saving the above script and executing it from sqlcmd, you can see:

This post is for day thirty-five of my blog series “A Month of SSMS Tips”. Yes, I’ve gone beyond a month – there’s just so many things that SSMS can help you with! I have a landing page for the series at bit.ly/MonthOfSSMS or at ssms.waynesheffield.com. Please visit this page for an easy place to quickly view all of the other tips in this series.

The post SSMS: Batch Separator (Day 35) appeared first on Wayne Sheffield.