SET options with indexed views

When I did this I made a change to the user options so that all connections would be made with ArithAbort on as by default the connections were made with this off. This can be done through QA using:

exec sp_configure 'show advanced options',1

reconfigure

exec sp_configure 'user options', 64

reconfigure

exec sp_configure 'show advanced options', 0

This has to be set when doing any insert/update or delete of the data. It caused me major headaches trying to troubleshoot a proc that was not working, only to find out that when the proc was created (before the indexed view) the ANSI_NULLS value was set incorrectly, recreating the proc the correct way resolved this....

From BOL:

Requirements for the CREATE INDEX Statement

The first index created on a view must be a unique clustered index. After the unique clustered index has been created, you can create additional nonclustered indexes. The naming conventions for indexes on views are the same as for indexes on tables. The only difference is that the table name is replaced with a view name. For more information, see CREATE INDEX.

The CREATE INDEX statement must meet these requirements in addition to the normal CREATE INDEX requirements:

• The user executing the CREATE INDEX statement must be the view owner.
• These SET options must be set to ON when the CREATE INDEX statement is executed:

  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNINGS
  • ARITHABORT
  • CONCAT_NULL_YIELDS_NULL
  • QUOTED_IDENTIFIERS

• The NUMERIC_ROUNDABORT option must be set to OFF.
• The view cannot include text, ntext, or image columns, even if they are not referenced in the CREATE INDEX statement.
• If the SELECT statement in the view definition specifies a GROUP BY clause, the key of the unique clustered index can reference only columns specified in the GROUP BY clause.

Considerations

After the clustered index is created, any connection attempting to modify the base data for the view must also have the same option settings required to create the index. SQL Server generates an error and rolls back any INSERT, UPDATE, or DELETE statement that will affect the result set of the view if the connection executing the statement does not have the proper option settings. For more information, see SET Options That Affect Results.