sql - 无法添加过滤索引但不明白为什么

Question

我正在尝试从 Visual Studio 中的数据库项目更新数据库。

更新脚本是自动生成的。在第 133 行，脚本添加了一个过滤索引。

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

但是这一行会产生一些错误:

(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.

首先，我真的不明白 ANSI_NULLS 的事情。通常，这只是一个无害的警告。

其次，我可以看到尝试添加过滤索引时失败，但我真的不明白为什么。检查数据，没有任何重复项会违反此唯一索引。

有人能指出我理解问题的正确方向吗？

Answer 1

来自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.