Menu
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 
898
When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET ANSI_NULLS settings are captured and used for subsequent invocations of that stored procedure. When executed inside a stored procedure, the setting of SET QUOTED_IDENTIFIER is not changed. When SET ANSI_DEFAULTS is ON, QUOTED_IDENTIFIER is also ON.
ANSI_NULLS should be set to ON for executing distributed queries. ANSI_NULLS must also be ON when you are creating or changing indexes on computed columns or indexed views. If SET ANSI_NULLS is OFF, any CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.
This setting is used to determine how quotation marks will be handled. When QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks and literals must be delimited by single quotation marks.
ANSI_NULLS define the comparison rule for NULL values in SQL Server. 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. Example.
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 IS (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 'some string'.
The proper way to handle this is with the COALESCE (or ISNULL) function: 'some string' + COALESCE(NULL, '') ...
132
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
