SET Options with their setting values required while working with filtered index

While dealing with filtered index following Six SET option’s value should be set to ON

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

And the one SET option whose setting value should be off is:

SET NUMERIC_ROUNDABORT OFF

If these settings are not set properly then filtered index creation may fail, query execution may not use the index or DML operation on the table may fail. The DML operation may fail with a error message like below:

INSERT/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.

Click on the error message link to see a detailed example demonstrating when we get this error and how to resolve it.

To know more on the difference between SET ANSI_NULLS ON and SET ANSI_NULLS OFF Setting please visit the article: SET ANSI_NULLS ON/OFF Setting in Sql Server

To know more on the difference between SET QUOTED_IDENTIFIER ON and SET QUOTED_IDENTIFIER OFF Setting please visit the article: Difference Between SET QUOTED_IDENTIFIER ON and OFF setting in SQL Server

Please correct me, if my understanding is wrong. Comments are always welcome.

3 thoughts on “SET Options with their setting values required while working with filtered index

  1. Thank you for posting this information. Today i deployed a filtered index and immediately insert stored procedures began failing. Wished i had read your article earlier 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *