Interesting issue with Filtered indexes.

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: https://msdn.microsoft.com/en-us/library/ms175049.aspx

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: 'ANSI_NULLS, 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 ANSI_NULLS and QUOTED_IDENTIFIER were set to an invalid setting.

The solution to the problem was to recreate the stored proc using the ‘correct’ settings required to use Filtered indexes, documented here: https://msdn.microsoft.com/en-us/library/ms188783.aspx

The following simplified TSQL example shows the problem and the solution.

NOTE: the fact that Stored Procedures were used is important, because they ‘inherit’ the SET statement values they were created with, and NOT the values they are executed with.

USE USE master

GO

CREATE DATABASE FI_Test

GO

USE FI_Test

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [FactSalesQuota](

      [SalesQuotaKey] [int] IDENTITY(1,1) NOT NULL,

      [EmployeeKey] [int] NOT NULL,

      [DateKey] [int] NOT NULL,

      [CalendarYear] [smallint] NOT NULL,

      [CalendarQuarter] [tinyint] NOT NULL,

      [SalesAmountQuota] [money] NOT NULL,

 CONSTRAINT [PK_FactSalesQuota_SalesQuotaKey] PRIMARY KEY CLUSTERED

([SalesQuotaKey] ASC))

GO

CREATE NONCLUSTERED INDEX FI_FactSalesQuota

ON FactSalesQuota(Employeekey,CalendarQuarter)

WHERE CalendarYear = 2009 -- <---- This makes it a Filtered index

GO

INSERT FactSalesQuota values(53,20090101,2009,4, 37000.00)

GO

SELECT * FROM FactSalesQuota

GO

--- this was the script to update the sp

use FI_Test

GO

sp_rename UPDATE_FactSalesQuota , UPDATE_FactSalesQuota_V1

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF -- <------ HERE is what caused the error

--SET QUOTED_IDENTIFIER ON -- <------ it will work if this is set correctly

GO

CREATE PROCEDURE UPDATE_FactSalesQuota

 @EmployeeKey int,

 @CalendarYear smallint,

 @CalendarQuarter tinyint,

 @SalesAmountQuota money

AS

UPDATE FactSalesQuota

 SET SalesAmountQuota = @SalesAmountQuota,

      DateKey = cast((CONVERT (char(8) ,getdate(), 112)) as int)

 WHERE EmployeeKey = @EmployeeKey and

            CalendarYear = @CalendarYear and

            CalendarQuarter = @CalendarQuarter

GO

--this fails

EXECUTE UPDATE_FactSalesQuota 53,2009,4,52000.00

GO

SELECT * FROM FactSalesQuota

GO

-- Now go back fix the script, and retry the sript and now it will work