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
OK, from an application developer's point of view, here's what these settings do:
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.
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
ColumnValue IS 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
The proper way to handle this is with the COALESCE (or ISNULL) function:
'some string' + COALESCE(NULL, '') ...