SQL Server – ALTER INDEX failed due to QUOTED IDENTIFIER set to OFF

Question: A regular index management job failed  and logged this  message. The index management job is run through the SQL Server Agent. :

ALTER INDEX 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 query notifications and/or xml data type methods. [SQLSTATE 42000]

How can I fix this issue?

Answer:   The underlying reason  is that SET 'QUOTED_IDENTIFIER'  is OFF. This index is relying on an indexed view , which means the error will occur.  Adding the  SET 'QUOTED_IDENTIFIER' to ON will fix the problem

According to SQL Server BOL “SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail”

 You may notice a disparity between executing the code through SSMS and SQL Server Agent.  If you  execute the code through SSMS , it will  most likely work – as by default SET 'QUOTED_IDENTIFIER' is set to ON.   By default , the SQL Server Agent does not set SET ‘QUOTED_IDENTIFIER’ or  ‘ARITHABORT’ .   

 To overcome the problem on SQL Server Agent , set the SET QUOTED_IDENTIFIER at the top of the script.

If you want to list the computed columns on a SQL Server table read: How to find computed columns on a SQL Server table

Read More

 SQL Server - Send sql server agent job history through email ...

SQL Agent Jobs – Schedule in seconds - SQL Server DBA

Top 5 SQL Server DMV for Index Analysis - SQL Server DBA