Create index failed because of ANSI_Padding

Viewed 7913

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

1 Answers
Related