title: Create index failed because of ANSI_Padding
body: <p>While I try to create an index in SQL Server 2008 R2, I get the following error.</p>
  
  <blockquote>
    <p>CREATE INDEX failed because the following SET options have incorrect settings: 'ANSI_PADDING'</p>
  </blockquote>
  
  <p>Index script is </p>
  
  <pre><code>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
  </code></pre>
  
  <p><a href="http://i.stack.imgur.com/8msHx.png" rel="noreferrer">Table definition is here</a>. In the table definition, you can find that there is no column with char, varchar, binary or varbinary. </p>
  
  <p>If I run index creation script with <code>SET ANSI_PADDING On</code>, it works. My question is why do I need to set this <code>On</code>, 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 <code>Off</code>. 
  <a href="http://i.stack.imgur.com/vbFkr.png" rel="noreferrer">ANSI padding for columns in this table</a></p>