Incorrect SET Options with Filtered Indexes

Rebuild Index|Create Index|DML on a Table with Filtered Index fails with the error:

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. [SQLSTATE 42000] (Error 1934).  The step failed.

The Operation above might succeed when its through SSMS. But, would fail when the operation is from a SQL Agent Job. This is because, SQL Agent by default, do not have the SET Options enabled (ON). They are OFF.

Reason:

The following are the SET Options that are needed while operation on a table with a filtered index.

Required SET Options for Filtered Indexes

The SET options in the Required Value column are required whenever any of the following conditions occur:

• Create a filtered index.
• INSERT, UPDATE, DELETE, or MERGE operation modifies the data in a filtered index.
• The query optimizer uses the filtered index in the query execution plan.
  

  SET options	Required value
  ANSI_NULLS	ON
  ANSI_PADDING	ON
  ANSI_WARNINGS*	ON
  ARITHABORT	ON
  CONCAT_NULL_YIELDS_NULL	ON
  NUMERIC_ROUNDABORT	OFF
  QUOTED_IDENTIFIER	ON

  *Setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON when the database compatibility level is set to 90 or higher. If the database compatibility level is set to 80 or earlier, the ARITHABORT option must explicitly be set to ON.

If the SET options are incorrect, the following conditions can occur:

• The filtered index is not created.
• The Database Engine generates an error and rolls back INSERT, UPDATE, DELETE, or MERGE statements that change data in the index.
• Query optimizer does not consider the index in the execution plan for any Transact-SQL statements.