New Information - SQL Server 2000 SP3.
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 statement in a batch invokes a trigger, 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.
Security Note Building certain kinds of strings dynamically may expose you to security vulnerabilities. Review all strings containing Transact-SQL statements to be executed as batches, strings to be executed by the EXECUTE statement, or strings to be executed by the sp_executesql stored procedure. If an application is concatenating values typed by end users into these strings, the application must first validate the user-supplied values before executing the string. For more information, see Validating User Input.