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