Batch Execution Environment and MARS
Beginning with Microsoft SQL Server 2005, connections can be enabled to support Multiple Active Result Sets (MARS). These connections have an associated default batch execution environment.
The batch execution environment is made up of the following components:
SET option values (including ANSI_NULLS, DATE_FORMAT, LANGUAGE, and TEXTSIZE)
Security context (the user or application role for the batch execution environment)
Database context (the current database for the environment)
Execution State functions (including @@ERROR, @@ROWCOUNT, @@FETCH_STATUS, and @@IDENTITY)
Top-level temporary tables
Every new batch that starts executing for a specific connection receives a copy of the default environment. Whenever a batch executes, all changes made to the execution environment are scoped to that specific batch. Once execution completes, the execution settings are copied into the default environment. In the case of a single batch issuing several commands to be executed sequentially, the behavior observed is the same as that exposed by connections involving clients or servers from previous releases of SQL Server.
In previous versions of SQL Server only one batch could execute at any given time. Therefore changes made to the batch environment by a batch were visible to all subsequent batches.
For stored procedures and functions, the default environment for a batch is the default for the connection, but when execution completes, changes are not copied back to the connection's default batch execution environment.
For example, assume that two batches execute as follows:
--First Batch
SET ARITHABORT ON
SET ANSI_WARNINGS ON
SELECT 1/0
GO
--Second Batch
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
SELECT 1/0
GO
Assume also that MARS is enabled for the connection with default execution environment, with SET ARITHABORT set to OFF and SET ANSI_WARNINGS set to ON.
If both batches are submitted concurrently under the connection, they start execution with the settings of the connection's default batch environment. During execution, their environments are scoped to ensure that environment changes in one batch do not affect the changes in the other batch. When a batch completes execution, it copies back the resulting environment to the connection's default. The resulting default environment for the connection depends on the last batch to complete execution. This will be the environment used by the next batch that is executed.