Recently, an ISV I work with ran into an interesting problem with Filtered Indexes.
The application does all SQL INSERT, DELETE, and UPDATE operations using individual stored procedures. To improve performance, they decided to create a Filtered Index (new in SQL 2008) to restrict the data such that it does not contain NULL values, in this case drastically reducing the number of rows in the index. See documentation here:
This all worked fine, until months later, when the ‘UPDATE’ stored procedure was updated as part of a routine application upgrade. The stored proc was replaced with a newer version using a TSQL script. After adding the ‘new’ stored proc the application was tested and the following was observed;
· the SELECT statements accessing the table continued to use the filtered index
· the ‘INSERT’, and ‘DELETE’ stored procs continued to work
· However, the modified ‘UPDATE’ stored proc returned the following error:
InnerException: System.Data.SqlClient.SqlException: UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. 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.
Note that the error message is very explicit and actually points us to the source of the problem. Can you guess what it is?
You are correct! The TSQL script used SET options that were set differently for the new ‘UPDATE’ stored proc than they were originally, and they don’t conform to the rules required to utilize Filtered Indexes. In this case, the SET QUOTED_IDENTIFIER was set to an invalid setting.