Create index failed because of ANSI_Padding

Question

While I try to create an index in SQL Server 2008 R2, I get the following error.

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

Index script is

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. In the table definition, you can find that there is no column with char, varchar, binary or varbinary.

If I run index creation script with SET ANSI_PADDING On, it works. My question is why do I need to set this On, where in the table definition, I don't have any column with char, varchar, binary or varbinary. You can also find that all these columns in this table have Ansi_padding setting Off. ANSI padding for columns in this table

Answer 1

Filtered indexes require 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