Thursday, January 5, 2012

Filtered Index and QUOTED_IDENTIFIER issue in SQL Server 2008

Today, I tried creating Filtered index on SQL Server database table. The error which was displayed is as below:

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

I then tried creating index with SET option QUOTED_IDENTIFIER ON and I was able to create filtered index.
I then searched internet to find the cause of issue and was able to find below article which shows interesting article related to issue of filtered index.

The reason I was getting error was that some stored procedures which were utilizing the filtered index had QUOTED_IDENTIFIER set to OFF which does not qualify to the rules required to utilize filtered indexes.

For using filtered indexes, it was required to set QUOTED_IDENTIFIER to ON in all the stored procedures utilizing that index.

Below is the query to find all the modules with Quoted_Identifier and Ansi_Null set to off.

FROM   sys.sql_modules m
JOIN   sys.objects o ON m.object_id = o.object_id
WHERE  (m.uses_quoted_identifier = 0 or
m.uses_ansi_nulls = 0)
AND  o.type NOT IN ('R', 'D')

No comments:

Post a Comment