SA0092 : The SQL module was created with ANSI_NULLS and/or QUOTED_IDENTIFIER options set to OFF

These options, when turned off, can lead to unexpected behavior in T-SQL queries. Proper understanding and setting of these options are crucial for ensuring consistency and compatibility within SQL Server environments.

For example:

1-- Example where ANSI_NULLS is OFF
2SET ANSI_NULLS OFF;
3SELECT * FROM Employees WHERE Name = NULL;

In this example, the query will not return any rows because with ANSI_NULLS OFF, comparisons against NULL using = results in a false outcome, which is not the case when ANSI_NULLS is ON as per best practices.

• ANSI_NULLS being OFF causes non-standard null comparison behavior, leading to maintenance challenges and potential bugs.

• When QUOTED_IDENTIFIER is OFF, quoted identifiers are treated like string literals, which can cause syntax errors or misinterpretation of queries, especially when working with reserved keywords.

To resolve issues related to the `sa0092` rule, ensure that the proper SQL Server session settings are applied to avoid unexpected T-SQL behavior.

Follow these steps to address the issue:

1. Identify the SQL modules where ANSI_NULLS and QUOTED_IDENTIFIER are set to OFF. This can be achieved by reviewing the module properties in SSMS or inspecting scripts used to create/alter the modules.

2. If the options are not required to be OFF, recreate or alter the SQL module in a session where ANSI_NULLS and QUOTED_IDENTIFIER are set to ON. Use the following commands in your T-SQL session:

   SQL

   Copy

   1SET ANSI_NULLS ON;
   2SET QUOTED_IDENTIFIER ON;

3. Re-execute the creation or alteration script for the SQL modules after setting the session options to ensure the settings are applied correctly.

4. Verify the settings by checking the properties of the SQL module in SSMS to confirm ANSI_NULLS and QUOTED_IDENTIFIER are ON.

5. Repeat this process for any other modules or scripts where these options need to be enforced.

For example:

 1-- Example of setting the correct options
 2SET ANSI_NULLS ON;
 3SET QUOTED_IDENTIFIER ON;
 4
 5-- Example of recreating a stored procedure
 6CREATE OR ALTER PROCEDURE [dbo].[GetEmployees]
 7AS
 8BEGIN
 9    SELECT * FROM Employees WHERE Name IS NULL; -- Correct null comparison
10END

The rule has a ContextOnly scope and is applied only on current server and database schema.

Rule has no parameters.

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Design Rules, Deprecated Features, Bugs

SET QUOTED_IDENTIFIER

SET ANSI_NULLS

Query Tuning Recommendations

Other Resources