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.

  1. SET options must be as such:

    SET optionsRequired valueDefault server valueDefault

    OLE DB and ODBC value

    DB-Library value
  2. Any functions you use in your view must be deterministic.

  3. Tables or UDFs must be referenced by two-part names, schema.tablename in the view definition.
  4. The view must be created by using the WITH SCHEMABINDING option.
  5. The view must reference only base tables that are in the same database as the view. The view cannot reference other views.
  6. 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