ANSI_NULLS and QUOTED_IDENTIFIER killed things. What are they for?

Question

NOTE: I checked Understanding QUOTED_IDENTIFIER and it does not answer my question.

I got my DBAs to run an index I made on my Prod servers (they looked it over and approved it).

It sped up my queries just like I wanted. However, I started getting errors like this:

As a developer I have usually ignored these settings. And it has never mattered. (For 9+ years). Well, today it matters.

I went and looked at one of the sprocs that are failing and it has this before the create for the sproc:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Can anyone tell me from a application developer point of view what these set statements do? (Just adding the above code before my index create statements did not fix the problem.)

NOTE: Here is an example of what my indexes looked like:

CREATE NONCLUSTERED INDEX [ix_ClientFilerTo0]
ON [ClientTable] ([Client])
INCLUDE ([ClientCol1],[ClientCol2],[ClientCol3] ... Many more columns)
WHERE Client = 0


CREATE NONCLUSTERED INDEX [IX_Client_Status]
ON [OrderTable] ([Client],[Status])
INCLUDE ([OrderCol1],[OrderCol2],[OrderCol3],[OrderCol4])
WHERE [Status] <= 7
GO

Answer 1

OK, from an application developer's point of view, here's what these settings do:

QUOTED_IDENTIFIER

This setting controls how quotation marks ".." are interpreted by the SQL compiler. When QUOTED_IDENTIFIER is ON then quotes are treated like brackets ([...]) and can be used to quote SQL object names like table names, column names, etc. When it is OFF (not recommended), then quotes are treated like apostrophes ('..') and can be used to quote text strings in SQL commands.

ANSI_NULLS

This setting controls what happens when you try to use any comparison operator other than IS on NULL. When it is ON, these comparisons follow the standard which says that comparing to NULL always fails (because it isn't a value, it's a Flag) and returns FALSE. When this setting is OFF (really not recommended) you can sucessfully treat it like a value and use =, <>, etc. on it and get back TRUE as appropiate.

The proper way to handle this is to instead use the IS (ColumnValue IS NULL ..).

CONCAT_NULL_YIELDS_NULL

This setting controls whether NULLs "Propogate" whn used in string expressions. When this setting is ON, it follows the standard and an expression like 'some string' + NULL .. always returns NULL. Thus, in a series of string concatenations, one NULL can cause the whole expression to return NULL. Turning this OFF (also, not recommended) will cause the NULLs to be treated like empty strings instead, so 'some string' + NULL just evaluates to 'some string'.

The proper way to handle this is with the COALESCE (or ISNULL) function: 'some string' + COALESCE(NULL, '') ...

Answer 2

I find the documentation, blog posts, Stackoverflow answers unhelpful in explaining what turning on QUOTED_IDENTIFIER means.

Olden times

Originally, SQL Server allowed you to use quotation marks ("...") and apostrophes ('...') around strings interchangeably (like Javascript does):

• SELECT "Hello, world!" --quotation mark
• SELECT 'Hello, world!' --apostrophe

And if you wanted a name table, view, procedure, column etc with something that would otherwise violate all the rules of naming objects, you could wrap it in square brackets ([, ]):

CREATE TABLE [The world's most awful table name] (
   [Hello, world!] int
)

SELECT [Hello, world!] FROM [The world's most awful table name]

And that all worked, and made sense.

Then came ANSI

Then ANSI came along and had other ideas:

• use apostrophe ('...') for strings
• if you have a funky name, wrap it in quotation marks ("...")
• and we don't even care about your square brackets

Which means that if you wanted to "quote" a funky column or table name you must use quotation marks:

SELECT "Hello, world!" FROM "The world's most awful table name"

If you knew SQL Server, you knew that quotation marks were already being used to represent strings. If you blindly tried to execute that ANSI-SQL as if it were T-SQL, it is nonsense:

SELECT 'Hello, world!' FROM 'The world''s most awful table name'

and SQL Server tells you so:

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'The world's most awful table name'.

You must opt-in to the new ANSI behavior

So Microsoft added a feature to let you opt-in to the ANSI flavor of SQL.

Original (aka SET QUOTED_IDENTIFIER OFF)

SELECT "Hello, world!" --valid
SELECT 'Hello, world!' --valid

SET QUOTED_IDENTIFIER ON

SELECT "Hello, world!" --INVALID
SELECT 'Hello, world!' --valid

These days everyone has SET QUOTED_IDENTIFIERS ON, which technically means you should be using quotes rather than square brackets around identifiers:

T-SQL (bad?) (e.g. SQL generated by Entity Framework)

UPDATE [dbo].[Customers]
SET [FirstName] = N'Ian'
WHERE [CustomerID] = 7

ANSI-SQL (good?)

UPDATE "dbo"."Customers"
SET "FirstName" = N'Ian'
WHERE "CustomerID" = 7

In reality nobody in the SQL Server universe uses U+0022 QUOTATION MARK " to wrap identifiers. We all continue to use [].

Answer 3

I think while rebuilding the indexes it got turned off.

Do check the SET Options with their setting values required while working with filtered index

You need to turn On the below setting while dealing with filtered index:

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON

You need add to add

SET ANSI_NULLS, QUOTED_IDENTIFIER ON

for all my stored procedures editing a table with a computed column to avoid that error.

ANSI_NULLS:

When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.

QUOTED_IDENTIFIER

When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers. For more information, see Database Identifiers. Literals can be delimited by either single or double quotation marks.

When SET QUOTED_IDENTIFIER is ON (default), all strings delimited by double quotation marks are interpreted as object identifiers. Therefore, quoted identifiers do not have to follow the Transact-SQL rules for identifiers. They can be reserved keywords and can include characters not generally allowed in Transact-SQL identifiers. Double quotation marks cannot be used to delimit literal string expressions; single quotation marks must be used to enclose literal strings. If a single quotation mark (') is part of the literal string, it can be represented by two single quotation marks ("). SET QUOTED_IDENTIFIER must be ON when reserved keywords are used for object names in the database.

CONCAT_NULL_YIELDS_NULL

When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, SELECT 'abc' + NULL yields NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string). For example, SELECT 'abc' + NULL yields abc.

If SET CONCAT_NULL_YIELDS_NULL is not specified, the setting of the CONCAT_NULL_YIELDS_NULL database option applies.

Answer 4

ANSI_NULLS ON makes any binary boolean expression with a null value evaluate to false. Using the following template:

declare @varA, @varB int

if <binary boolean expression>
begin
    print 'true'
end
else
begin
    print 'false'
end


@varA: NULL; @varB: NULL; @varA = @varB evaluates to false
@varA: 1; @varB: NULL; @varA <> @varB evaluates to false

The proper way to test for null is to use is [not] NULL

@varA: NULL; @varA is NULL evaluates to true
@varA: 1; @varA is not NULL evaluates to true

QUOTED_IDENTIFER ON merely allows you to use double quotes to delimit identifiers (bad idea IMO, just user square brackets)

from tblA "a" -- ok when ON, not ok when OFF
from tblA [a] -- always ok