Geek Answers Handbook

Index creation failed due to ANSI_Padding

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

CREATE INDEX error because the following SET parameters have incorrect settings: "ANSI_PADDING"

The script index 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

The definition of the table is here . In the table definition, you may find that there is no column with char, varchar, binary, or varbinary.

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

+4
1

ANSI PADDING ON.

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

SET

SET :

  • .
  • INSERT, UPDATE, DELETE MERGE .
  • .

SET

ANSI_NULLS ON

ANSI_PADDING ON

ANSI_WARNINGS * ON

ARITHABORT ON

CONCAT_NULL_YIELDS_NULL ON

NUMERIC_ROUNDABORT OFF

QUOTED_IDENTIFIER ON

+6

More articles:


All Articles