Skip to content

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

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

In SQL Server, ANSI_NULLS and QUOTED_IDENTIFIER settings play crucial roles in how SQL queries are interpreted and executed. Setting these options to OFF can lead to inconsistent behavior and compatibility issues, especially when relying on functions like IS NULL for NULL comparisons or when handling identifiers that require quotations.

For example:

-- Incorrect setting of ANSI_NULLS
SET ANSI_NULLS OFF;
SELECT column FROM Table WHERE column = NULL;
-- Incorrect setting of QUOTED_IDENTIFIER
SET QUOTED_IDENTIFIER OFF;
SELECT * FROM "TableName";

The problematic aspect of these examples is:

  • Setting ANSI_NULLS to OFF causes the = operator to behave incorrectly with NULL , leading to unexpected results in queries.

  • Disabling QUOTED_IDENTIFIER can prevent the correct interpretation of identifiers enclosed in double quotes, potentially causing syntax errors or data access issues.

Ensure proper use of ANSI_NULLS and QUOTED_IDENTIFIER settings to prevent inconsistent behavior and compatibility issues in SQL scripts.

Follow these steps to address the issue:

1.Review and identify scripts where ANSI_NULLS or QUOTED_IDENTIFIER is set to OFF .

2.Modify these scripts to set ANSI_NULLS and QUOTED_IDENTIFIER to ON as follows:

3.Recreate the SQL module or session ensuring that both ANSI_NULLS and QUOTED_IDENTIFIER are ON .

4.Also, ensure other relevant settings such as ANSI_PADDING , ANSI_WARNINGS , ARITHABORT , and CONCAT_NULL_YIELDS_NULL are set to ON , and NUMERIC_ROUNDABORT is set to OFF .

For example:

-- Correct settings for module creation
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
GO
CREATE PROCEDURE ExampleSP
AS
SELECT * FROM SomeTable WHERE Column1 IS NULL;

The rule has a Batch scope and is applied only on the SQL script.

Name Description Default Value
CanWorkWithoutSqlConnection Parameters specifies whether the rule can work without SQL Connection. yes
QuotedIdentifierDefaultState Default state of the QUOTED_IDENTIFIER option that is used in case no SQL connection is available. ON
AnsiNullsDefaultState Default state of the Default state of the ANSI_NULLS option that is used in case no SQL connection is available. option that is used in case no SQL connection is available. ON

The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

20 minutes per issue.

Design Rules, Bugs

SET QUOTED_IDENTIFIER

SET ANSI_NULLS

Query Tuning Recommendations

-----------
-- Test of the rule is not possible as it requires script in more than one batch
-----------
--set quoted_identifier off
--go
--set ansi_nulls on
--go
create procedure aaa
as
select 1

No violations found.

Analysis Rules