There are plenty of questions here on SO and articles online about how to handle this mess, but my question is more fundamental: WHY? Quoted identifier seems to be a property of how the engine interprets queries, and nothing to do with the data within. As far as the index and insert/update queries are concerned, who cares? Why does this setting matter?
Suppose you allowed inserts/updates to succeed with quoted_identifier set to OFF in a table with a filtered index - what exactly would break?
SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, then CREATE, UPDATE, INSERT, and DELETE statements will fail on tables with indexes on computed columns, or tables with indexed views.
A filtered index reduces index maintenance costs compared with a full-table nonclustered index because it is smaller and is only maintained when the data in the index is changed. It is possible to have a large number of filtered indexes, especially when they contain data that is changed infrequently.
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.
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.
The error message contains a clue:
"Verify that SET options are correct for use with
indexed views and/or
indexes on computed columns and/or
filtered indexes and/or
query notifications and/or
XML data type methods and/or
spatial index operations."
There is a single set of canonical SET options requires across all of these features. It may be that for inserts into tables with filtered indexes QUOTED_IDENTIFIERS could work, since, as you say, it only affects the parsing of query text. But one of these other features couldn't work with QUOTED_IDENTIFIERS off.
And it may be as mundane as the fact that QUOTED_IDENTIFERS is such an odd duck* in query parsing (and turning it off is not recommended) that supporting it in these scenarios is simply not worth the effort.
*see https://learn.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql which explains the differences in the session setting, its behavior in a top-level ad-hoc batch, a stored procedure, and a nested batch.
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