Create a filtered index that will work with ANSI_NULLS OFF

Question

I am working today on an old Delphi application that was written over 10 years ago. Parts of the application contain components that will construct update statements in the application and then send them to the SQL Server to be run.

The work I needed to do was to try to speed up a few queries. To do this I added two indexes that look like this:

CREATE NONCLUSTERED INDEX [ix_ClientFilerTo0]
ON [ClientTable] ([Client])
INCLUDE ([ClientCol1],[ClientCol2],[ClientCol3] ... Many more columns)
WHERE Client = 0


CREATE NONCLUSTERED INDEX [IX_Client_Status]
ON [OrderTable] ([Client],[Status])
INCLUDE ([OrderCol1],[OrderCol2],[OrderCol3],[OrderCol4])
WHERE [Status] <= 7
GO

When I did that I got the following error:

I tried adding those settings to my index create statements, but it did not help.

I ran an SQL Profile and my application's connection was listed to have the following settings:

set quoted_identifier off
set arithabort off
set numeric_roundabort off
set ansi_warnings off
set ansi_padding off
set ansi_nulls off
set concat_null_yields_null off
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

I think that these settings are coming from the very old version of the BDE this app uses. It would be a lot of work to change them (and we are trying to put our effort into rewriting this app).

Is there any way to create the index such that it will work with a connection that has these settings?

NOTES:

• I am using SQL Server 2012.
• This error happens on both my Prod and Non-Prod SQL Servers
• This error happens on many users machines

Here is an example of an index that is currently on my Orders Table:

USE [UseMyDb]
GO

/****** Object:  Index [IX_AnotherIndex]    Script Date: 10/3/2013 2:56:49 PM ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_AnotherIndex] ON [OrderTable]
(
    [Sequence] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SomethingIDontUnderstand]
GO

Answer 1

There's no work around. See CREATE INDEX:

Required SET Options for Filtered Indexes

The SET options in the Required Value column are required whenever any of the following conditions occur:

...

• INSERT, UPDATE, DELETE, or MERGE operation modifies the data in a filtered index.

So the correct options have to be set when the UPDATE statement is issued. Your settings were already correct when you created the index (or you wouldn't have been allowed to)