CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
Vaccano
Vaccano

Reputation: 82377

Create a filtered index that will work with ANSI_NULLS OFF

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:

UPDATE failed because the following SET options have incorrect settings: ANSI_NULL, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NUL

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:

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

Upvotes: 4

Views: 4208

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239724

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)

Upvotes: 3

Zelloss
Zelloss

Reputation: 568

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.

Upvotes: 0

Related Questions