ANSI_NULLS and QUOTED_IDENTIFIER killed things. What are they for?
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, '') ...
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 markSELECT '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 [].
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.