Managing Databases and Queries with Batches and Scripts

A T-SQL query can contain 0 to almost any number of SELECT statements (within reason). A set of T-SQL statement that can be executed sequentially is called a "batch". These statements can be SELECT statements, local variable declaration, logic statementsany T-SQL. Building your own batches is easysimply concatenate the T-SQL statements together. No, you don't need to add a semicolon between the statements unless it makes you happybut you do need to be aware that there are several rules that dictate which commands can be combined into a script. The entire batch is compiled into a single execution plan that might positively or negatively affect performance.

For example, the T-SQL statement in Figure 2.66 returns two resultsetseach containing a single rowset generated by the two SELECT statements.

Figure 2.66. Returning multiple resultsets from a single T-SQL batch.

Once a resultset has been (completely) processed, ADO.NET can ask SQL Server if there are more resultsets and the process can be repeated.

Scripts Versus Batches

In some cases, one finds the need to create a file that contains a set of batches to perform a series of T-SQL operationsthis is called a "script". DBAs often use scripts to create entire databases or orchestrate complex changes to the schema.

When creating a script, each batch is separated from the next with a "break" operator (usually "GO" on a separate line). T-SQL is not capable of executing batches on its own, so you'll have to use one of the tools such as SQLCMD, OSQL, ISQL, SQL Server Management Studio, or SQL Server Management Studio Express. See "GO" in BOL for more details.

As I'll show in a minute, if the T-SQL in your batch is too complex, SQL Server can spend a lot of time trying to compile and execute it. A T-SQL batch can also contain other commands. It can contain "action" statements (as I'll discuss in a minute) that make changes to the data or commands that control utilities like DBCC^[21] on the server. It can also contain stored procedures to be executed on the server. As I discuss in a few pages, these are simply pre-compiled T-SQL programs you've written ahead of time to improve performance. A SELECT statement that passes a syntax check rarely fails, but if it does, it's usually because you don't have rights to examine a specific part of the data you've asked to retrieve.

^[21] DBCC is a utility that can be called from a T-SQL query. It's used to perform a wide variety of maintenance and diagnostic functions on the server.