Unable to specify FILLFACTOR on clustered index in SQL Server (ANSI_PADDING error)

Question

Why can't FILLFACTOR be set on a clustered index with PERSISTED NOT NULL computed columns?

Query to set FILLFACTOR:

ALTER INDEX [PK_MyIndex] ON [MyTable] REBUILD PARTITION=ALL WITH (FILLFACTOR = 90)

Error:

ALTER INDEX failed because the following SET options have incorrect settings: 'ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Is it possible to get this to work?

Answer 1

Please read the following article: https://msdn.microsoft.com/en-AU/library/ms187403.aspx

SET ANSI_PADDING must be ON when you are creating or changing indexes on computed columns or indexed views. For more information about required SET option settings with indexed views and indexes on computed columns, see "Considerations When You Use the SET Statements" in SET Statements (Transact-SQL).

Try the following statement:

SET ANSI_PADDING ON
ALTER INDEX [PK_MyIndex] ON [MyTable] REBUILD PARTITION=ALL WITH (FILLFACTOR = 90)