Batch Processing

A batch is a collection of one or more SQL statements sent in one unit by the client. Each batch is compiled into a single execution plan. If the batch contains multiple SQL statements, all of the optimized steps needed to perform all the statements are built into a single execution plan.

There are several ways to specify a batch:

• All the SQL statements sent in a single execution unit from an application comprise a single batch and generate a single execution plan. For more information about how an application specifies a batch, see Batches.
• All the statements in a stored procedure or trigger comprise a single batch. Each stored procedure or trigger is compiled into a single execution plan.
• The string executed by an EXECUTE statement is a batch compiled into a single execution plan.
• The string executed by an sp_executesql system stored procedure is a batch compiled into a single execution plan.

When a batch sent from an application contains an EXECUTE statement, the execution plan for the executed string or stored procedure is executed separately from the execution plan containing the EXECUTE statement. The execution plan generated for the string executed by an sp_executesql stored procedure also remains separate from the execution plan for the batch containing the sp_executesql call. If a trigger is fired by a statement in a batch, the trigger execution plan executes separately from the original batch.

For example, a batch that contains these four statements uses five execution plans:

• An EXECUTE statement executing a stored procedure.
• An sp_executesql call executing a string.
• An EXECUTE statement executing a string.
• An UPDATE statement referencing a table that has an update trigger.