Changing SET options within a batch in SQL Server can lead to unexpected behavior, performance issues, and compatibility problems. It's strongly recommended to avoid altering SET options mid-batch for several reasons:

1. Plan Caching and Recompilation

• Inconsistent Execution Plans: Changing SET options within a batch can affect the query execution plan. SQL Server might cache and reuse execution plans based on the SET options at compile time. Changing these options mid-batch can lead to plan cache pollution, where inappropriate plans are used, or excessive recompilations, both of which degrade performance.

• Plan Stability: Keeping SET options consistent ensures that SQL Server can more reliably cache and reuse execution plans, improving the efficiency of query processing.

2. Transaction Integrity

• Changing certain SET options within a transaction can lead to unexpected results or behavior. For example, altering SET XACT_ABORT influences the handling of transactions on error conditions, potentially affecting transaction integrity.

3. Index Usage

• Some SET options, like SET ANSI_NULLS, directly impact the use of indexes, including indexed views. Changing these options within a batch may cause SQL Server to bypass otherwise optimal indexes, leading to slower query performance.

4. Query Results Consistency

• Options such as SET DATEFIRST or SET LANGUAGE affect the interpretation of dates and localized settings. Altering these within a batch can result in inconsistent query results, making data analysis and reporting more challenging.

5. Compatibility and Best Practices

• Consistent SET options ensure compatibility across different parts of an application and adherence to best practices. Changing options mid-batch can introduce subtle bugs, especially in complex applications where batches may not be executed in isolation.

• Stored Procedures and Functions: SQL Server uses the SET options active at the time of a stored procedure or function's creation to influence their execution. Inconsistent SET options can lead to unexpected behavior when these objects are used.

Best Practices

• Session-Level Consistency: Set the required SET options at the start of the session or connection to ensure consistency across the entire workload.

• Testing and Validation: If changes to SET options are necessary, thoroughly test and validate the impact on the application to ensure there are no adverse effects.

• Documentation and Standards: Document the SET options required for your application and enforce these as standards to avoid unintended changes.

In conclusion, while SQL Server allows changing SET options within a batch, doing so is generally discouraged because of the potential for negative impacts on query performance, execution plan stability, and result consistency. Maintaining a consistent environment for your SQL Server workloads ensures reliability and performance.