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, '') ...