SQL-Query Basics  «Prev  Next»
Lesson 3 Batch queries
Objective Describe batch queries, and compare them to single queries.

Describe SQL Batch Queries

What is a batch?

batch[1] is a group of SQL statements that are executed at one time by SQL Server.
These statements are sent to SQL Server by a program, such as the Query Analyzer.
The opposite of a batch query is a single query, containing only one SQL statement.
If there are no GO keywords in a set of Transact-SQL statements, they are all considered to be in one batch.

Advantage of batch queries

If an error occurs while executing one statement in a batch, SQL Server will not execute the remaining statements. This will help you troubleshoot any problems in your batch files.

When using variables

Each batch is treated as a single unit, and statements in one batch have no knowledge of statements in another batch. Therefore, if you use a variable in a batch, you must declare it within that batch.

When not to batch

The following CREATE SQL statements can only be executed as single statements:
  1. CREATE DEFAULT
  2. CREATE PROC or CREATE PROCEDURE
  3. CREATE RULE
  4. CREATE TRIGGER
  5. CREATE VIEW
These statements cannot be batched because SQL Server does not allow the creation of database objects within a batch.

Creating a batch: the GO statement

All statements that are listed in the Query Analyzer between GO statements are considered to be batches. However, if there are no GO statements, all statements that you type into the Query Analyzer are considered to be a batch. This can be thought of as an implicit GO statement at the very end of all the statements that you send to SQL Server. For example, both statements below are treated as a single batch: 

UPDATE Employees
SET FirstName = 'Anthony',
LastName = 'Mann'
WHERE EmployeeID = 101
SELECT *
FROM Employees
WHERE EmployeeID = 101

The following set of statements is treated as two separate batches because they are separated by the GO statement: 
USE Timesheets
GO
UPDATE Employees
SET FirstName = 'Anthony',
LastName = 'Mann'
WHERE EmployeeID = 101
SELECT *
FROM Employees
WHERE EmployeeID = 101

In the next lesson, the power of SQL Server system catalogs will be discussed.
[1] Batch: A set of Transact-SQL commands that are sent to the SQL Server engine, all at one time. Batches are separated by GO keywords.