SA0092B : The SQL module was created with ANSI_NULLS and/or QUOTED_IDENTIFIER options set to OFF
Introduction
Section titled “Introduction”Setting ANSI_NULLS and/or QUOTED_IDENTIFIER to OFF in SQL modules can lead to inconsistent behavior, incorrect results, and errors.
Description
Section titled “Description”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_NULLSSET ANSI_NULLS OFF;SELECT column FROM Table WHERE column = NULL;
-- Incorrect setting of QUOTED_IDENTIFIERSET QUOTED_IDENTIFIER OFF;SELECT * FROM "TableName";The problematic aspect of these examples is:
-
Setting
ANSI_NULLStoOFFcauses the=operator to behave incorrectly withNULL, leading to unexpected results in queries. -
Disabling
QUOTED_IDENTIFIERcan prevent the correct interpretation of identifiers enclosed in double quotes, potentially causing syntax errors or data access issues.
How to fix
Section titled “How to fix”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 creationSET QUOTED_IDENTIFIER ON;SET ANSI_NULLS ON;GOCREATE PROCEDURE ExampleSPASSELECT * FROM SomeTable WHERE Column1 IS NULL;The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”| 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 |
Remarks
Section titled “Remarks”The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.
Effort To Fix
Section titled “Effort To Fix”20 minutes per issue.
Categories
Section titled “Categories”Design Rules, Bugs
Additional Information
Section titled “Additional Information”Example Test SQL
Section titled “Example Test SQL”------------- 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--gocreate procedure aaaasselect 1Analysis Results
Section titled “Analysis Results”No violations found.