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

Introduction

Setting ANSI_NULLS and/or QUOTED_IDENTIFIER to OFF in SQL modules can lead to inconsistent behavior, incorrect results, and errors.

Description

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:

-- Example where ANSI_NULLS is OFF
SET ANSI_NULLS OFF;
SELECT * 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.

How to fix

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:

        SET ANSI_NULLS ON;
        SET 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:

-- Example of setting the correct options
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;

-- Example of recreating a stored procedure
CREATE OR ALTER PROCEDURE [dbo].[GetEmployees]
AS
BEGIN
    SELECT * FROM Employees WHERE Name IS NULL; -- Correct null comparison
END

Scope

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

Parameters

Rule has no parameters.

Remarks

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

Effort To Fix

20 minutes per issue.

Additional Information

SET QUOTED_IDENTIFIER

SET ANSI_NULLS

Query Tuning Recommendations

Analysis Rules