So creating indexed views on SQL Server requires you to verify that the view adheres to certain necessary conditions. These are many. It makes sense though, as a view is dynamic and these conditions are necessary to guarantee that the view will return the same data across successive calls.
Anyways, I've put the list of necessary conditions here that are important to me. See here for the full scoop.
SET options must be as such:
SET options Required value Default server value Default
OLE DB and ODBC value
ANSI_NULLS ON ON ON OFF ANSI_PADDING ON ON ON OFF ANSI_WARNINGS* ON ON ON OFF ARITHABORT ON ON OFF OFF CONCAT_NULL_YIELDS_NULL ON ON ON OFF NUMERIC_ROUNDABORT OFF OFF OFF OFF QUOTED_IDENTIFIER ON ON ON OFF
Any functions you use in your view must be deterministic.
- Tables or UDFs must be referenced by two-part names, schema.tablename in the view definition.
- The view must be created by using the WITH SCHEMABINDING option.
- The view must reference only base tables that are in the same database as the view. The view cannot reference other views.
- Your view cannot contain the following: COUNT [but can use COUNT_BIG], SUM, MIN, MAX, AVG, DISTINCT, TOP, UNION, OUTER APPLY, CROSS APPLY, OUTER joins, Self-joins, HAVING, a CTE, a subquery