Unable to Add Filtered Index but don't Understand Why

Question

I am trying to update a database from a database project in Visual Studio.

The update script is generated automatically. On line 133, the script adds a filtered index.

CREATE UNIQUE NONCLUSTERED INDEX [ProgramCodes_Value]
    ON [dbo].[ProgramCodes]([Value] ASC) WHERE ([IsDeleted]=(0));

But this line produces some errors:

(133,1): SQL72014: .Net SqlClient Data Provider: Msg 1935, Level 16, State 1, Line 1 Cannot create index. Object 'ProgramCodes' was created with the following SET options off: 'ANSI_NULLS'.

(133,0): SQL72045: Script execution error. The executed script: CREATE UNIQUE NONCLUSTERED INDEX [ProgramCodes_Value] ON [dbo].[ProgramCodes]([Value] ASC) WHERE ([IsDeleted] = (0));

An error occurred while the batch was being executed.

First off, I really don't understand the ANSI_NULLS thing. Usually, this is just a harmless warning.

Second, I can see it failed when trying to add the filtered index, but I really can't see why. Checking the data, there are no duplicates that would violate this unique index.

Can anyone point me in the right direction to understanding the problem?

Answer 1

From the docs:

SET ANSI_NULLS must also be ON when you are creating or changing indexes on computed columns or indexed views. If SET ANSI_NULLS is OFF, any CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. SQL Server will return an error that lists all SET options that violate the required values. Also, when you execute a SELECT statement, if SET ANSI_NULLS is OFF, SQL Server will ignore the index values on computed columns or views and resolve the select operation as if there were no such indexes on the tables or views.