In one line: the optimizer can only use a filtered index when the filter’s value is written into the query, not passed as a parameter, and once a table has one, every write to it needs QUOTED_IDENTIFIER and ANSI_NULLS on.
Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/filtered-index on GitHub.
What you’ll see
- A query that uses the filtered index in SSMS and scans in the application. In the demo: 31 logical reads with the value written in, 14,598 with the same value as a parameter.
- An
UnmatchedIndexeswarning in the plan, naming the filtered index. - Error 1934: “INSERT failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER'”, from code that worked until the filtered index was added.
- Statistics on the filtered index that don’t update although its rows grew 20-fold.
Why it happens
A cached plan is reused for every value of its parameters. A filtered index on Status = 1 holds no rows for Status = 4, so a plan that reads it would be wrong for other values, and the optimizer can’t choose it for Status = @s. That includes local variables and forced parameterization, which turns literals into parameters.
The index is maintained on every insert, update and delete, so SQL Server requires the same SET options as for indexed views and indexes on computed columns, for every write to the table, even rows outside the filter. A procedure keeps the QUOTED_IDENTIFIER setting it was created with, so an old procedure can fail even when the caller’s session is set correctly.
Statistics update automatically after a number of changes based on the whole table’s size, not the filtered index’s.
What to do
- Write the filtered column’s value into the query text:
WHERE Status = 1 AND CustomerID = @c. Parameters on other columns are fine. - Use
OPTION (RECOMPILE)where the filtered value has to be a variable and the query runs rarely enough to pay for a compilation every time. - Before adding a filtered index, find the procedures that write to the table and were created with
QUOTED_IDENTIFIERorANSI_NULLSoff (the query below), and recreate them with both on. - Update a filtered index’s statistics on a schedule of their own.
What doesn’t help
- Forced parameterization. It stops the filtered index from being used by queries that could use it before.
- Setting
QUOTED_IDENTIFIER ONin the calling session for a procedure created with it off.
Check your server
-- Modules in the current database created with QUOTED_IDENTIFIER or
-- ANSI_NULLS off: they will fail with error 1934 on any write to a table
-- with a filtered index.
SELECT OBJECT_SCHEMA_NAME(m.object_id) + '.' + OBJECT_NAME(m.object_id) AS module_name,
o.type_desc, m.uses_quoted_identifier, m.uses_ansi_nulls
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON o.object_id = m.object_id
WHERE (m.uses_quoted_identifier = 0 OR m.uses_ansi_nulls = 0)
AND o.is_ms_shipped = 0;
-- Filtered indexes, and how many changes their statistics haven't seen.
SELECT OBJECT_SCHEMA_NAME(i.object_id) + '.' + OBJECT_NAME(i.object_id) AS table_name,
i.name AS index_name, i.filter_definition,
sp.rows, sp.modification_counter, sp.last_updated
FROM sys.indexes AS i
CROSS APPLY sys.dm_db_stats_properties(i.object_id, i.index_id) AS sp
WHERE i.has_filter = 1;
See it happen
The filtered-index demo runs the same query five ways and shows:
- the filtered index used with a literal, and not with a variable, an
sp_executesqlparameter or forced parameterization - the
UnmatchedIndexeswarning - error 1934 from an insert, an update and a procedure
- the filtered index’s statistics left at 500 rows when it held 10,500
Related: slow in the app, fast in SSMS · parameter sniffing · catch-all queries
Measured on SQL Server 2025 RTM-CU10 (17.0.5035.5) and SQL Server 2022 RTM-CU25 (16.0.4255.1), Linux containers. Your numbers will differ; the behaviour shouldn’t.
Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.
