Why must quoted_identifier be ON to use a filtered index?

Question

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?

Formosa

Answer 1

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.

Creatinine