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

Can you use scripting options that show index options for indexes that already work? — Rob, Oct 03 '13 at 20:53
@Rob - I updated with an example of a current index on my table. — Vaccano, Oct 03 '13 at 21:00
related: http://stackoverflow.com/questions/19168179/ansi-nulls-and-quoted-identifier-killed-things-what-are-they-for — bummi, Oct 03 '13 at 21:06
@bummi - yeah I asked that about an hour ago. That question was focused on what those setting do. This one is about what I can do to make it work. — Vaccano, Oct 03 '13 at 21:07
that's why I wrote related, not duplicate, to avoid unneeded researches in direction of possibly already given answers. — bummi, Oct 03 '13 at 21:10
@Vaccano http://connect.microsoft.com/SQLServer/feedback/details/603334/remove-ansi-setting-restrictions-rom-filtered-indexes — brian, Oct 04 '13 at 00:35
There's no work around. See [`CREATE INDEX`](http://technet.microsoft.com/en-us/library/ms188783.aspx): "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) — Damien_The_Unbeliever, Oct 04 '13 at 07:30
@Damien_The_Unbeliever - I agree. If you post this as an answer I will accept. — Vaccano, Oct 04 '13 at 18:55
@Damien_The_Unbeliever: Seconding Vaccano's suggestion: in my opinion, yours *is* the answer to this question. — Andriy M, Oct 11 '13 at 06:15
Sorry - it was one I meant to come back to and add with suitable formatting when I had time. Done now. — Damien_The_Unbeliever, Oct 11 '13 at 07:17

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

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)

score 0 · Answer 2 · answered Oct 10 '13 at 07:54

I can't reproduce the problem so I imagine that it's related to some of the data not compliying with the settings when you create the index (legacy, huh? been there! :) ). If that's the case you can specify a different set of settings only to the connection in which you are creating the index. Try this:

set quoted_identifier on
set ansi_nulls on
set concat_null_yields_null on

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

The connection from delphi will still be using its settings, but this could allow you to create the indexes.

Create a filtered index that will work with ANSI_NULLS OFF

2 Answers2