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: http://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: http://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
CREATE DATABASE FI_Test
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
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
CREATE NONCLUSTERED INDEX FI_FactSalesQuota
WHERE CalendarYear = 2009 -- <---- This makes it a Filtered index
INSERT FactSalesQuota values(53,20090101,2009,4, 37000.00)
SELECT * FROM FactSalesQuota
--- this was the script to update the sp
sp_rename UPDATE_FactSalesQuota , UPDATE_FactSalesQuota_V1
SET QUOTED_IDENTIFIER OFF -- <------ HERE is what caused the error
--SET QUOTED_IDENTIFIER ON -- <------ it will work if this is set correctly
CREATE PROCEDURE UPDATE_FactSalesQuota
SET SalesAmountQuota = @SalesAmountQuota,
DateKey = cast((CONVERT (char(8) ,getdate(), 112)) as int)
WHERE EmployeeKey = @EmployeeKey and
CalendarYear = @CalendarYear and
CalendarQuarter = @CalendarQuarter
EXECUTE UPDATE_FactSalesQuota 53,2009,4,52000.00
-- Now go back fix the script, and retry the sript and now it will work