First published on MSDN on Jun 29, 2009
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: '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 QUOTED_IDENTIFIER was 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
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) ON [PRIMARY]
) ON [PRIMARY]
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
CREATE PROCEDURE UPDATE_FactSalesQuota
@EmployeeKey int,
@CalendarYear smallint,
@CalendarQuarter tinyint,
@SalesAmountQuota money
AS
UPDATE FactSalesQuota
SET SalesAmountQuota = @SalesAmountQuota
WHERE EmployeeKey = @EmployeeKey and
CalendarYear = @CalendarYear and
CalendarQuarter = @CalendarQuarter
GO
EXECUTE UPDATE_FactSalesQuota 53,2009,4,50000.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
Cross Posted from
http://blogs.microsoft.com/mssqlisv
