SA0092B : The SQL module was created with ANSI_NULLS and/or QUOTED_IDENTIFIER options set to OFF
The topic describes the SA0092B analysis rule.
The SQL module was created with ANSI_NULLS and/or QUOTED_IDENTIFIER options set to OFF
The rule checks SQL script for stored procedures, triggers, functions or views being created/modified with ANSI_NULLS, and/or QUOTED_IDENTIFIER set to OFF.
Consider reviewing the need for these options settings, and in case they are not required, you should recreate the SQL module using a session that has both these options set to ON.
Even these settings may not currently relate performance problems, they may prevent further performance optimizations, such as filtered indexes or indexed views.
ANSI_NULLS is one of seven SET options that must be set to required values when dealing with indexes on computed columns or indexed views. The options ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, and CONCAT_NULL_YIELDS_NULL must also be set to ON, and NUMERIC_ROUNDABORT must be set to OFF.
1SET QUOTED_IDENTIFIER ON 2SET ANSI_NULLS ON 3GO 4CREATE PROCEDURE ExampleSP 5AS 6SELECT * FROM "SomeTable" WHERE Column1 = NULL
The rule has a Batch scope and is applied only on the SQL script.
Rule has no parameters.
The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.
Design Rules, Bugs
1----------- 2-- Test of the rule is not possible as it requires script in more than one batch 3----------- 4 5--set quoted_identifier off 6--go 7--set ansi_nulls on 8--go 9create procedure aaa 10as 11select 1
No violations found.