sql-server - 由于 ANSI_Padding 创建索引失败

Question

当我尝试在 SQL Server 2008 R2 中创建索引时，出现以下错误。

CREATE INDEX failed because the following SET options have incorrect settings: 'ANSI_PADDING'

索引脚本是

CREATE UNIQUE NONCLUSTERED INDEX [IX_TabPermission_Roles] ON [dbo].         
[TabPermission] 
(
    [RoleID] ASC,
    [TabID] ASC,
    [PermissionID] ASC
)
INCLUDE ( [AllowAccess]) 
WHERE ([RoleID] IS NOT NULL)
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 [PRIMARY]
GO

Table definition is here 。在表定义中，可以发现没有包含 char、varchar、binary 或 varbinary 的列。

如果我使用SET ANSI_PADDING On运行索引创建脚本，它就可以工作。我的问题是为什么我需要设置此 On，在表定义中，我没有任何包含 char、varchar、binary 或 varbinary 的列。您还可以发现此表中的所有这些列都具有 Ansi_padding 设置 Off。 ANSI padding for columns in this table

Answer 1

过滤索引需要 ANSI PADDING ON。

https://msdn.microsoft.com/en-us/library/ms188783(v=sql.105).aspx

Required SET Options for Filtered Indexes

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

• Create a filtered index.
• INSERT, UPDATE, DELETE, or MERGE operation modifies the data in a filtered index.
• The query optimizer uses the filtered index in the query execution plan.

SET options Required value

ANSI_NULLS ON

ANSI_PADDING ON

ANSI_WARNINGS* ON

ARITHABORT ON

CONCAT_NULL_YIELDS_NULL ON

NUMERIC_ROUNDABORT OFF

QUOTED_IDENTIFIER ON