SQL Server Indexed Views

Q. Why are there restrictions on the kind of views I can create an index on?

A. To make sure that it is logically possible to incrementally maintain the view, to restrict the ability to create a view that would be expensive to maintain, and to limit the complexity of the SQL Server implementation. A large set of views is nondeterministic and context-dependent; their contents 'change' independently of DML operations. These can't be indexed. Examples are any views that call GETDATE or SUSER_SNAME in their definition.

Q. Why does the first index on a view have to be CLUSTERED and UNIQUE?

A. It must be UNIQUE to allow easy lookup of records in the view by key value during indexed view maintenance, and to prevent creation of views with duplicates, which would require special logic to maintain. It must be clustered because only a clustered index can enforce uniqueness and store the rows at the same time.

Q. Why isn't my indexed view being picked up by the query optimizer for use in the query plan?

A. There are three primary reasons the indexed view may not be being chosen by the optimizer:

• You are using a version other than Enterprise or Developer edition of SQL Server. Only Enterprise and Developer editions support automatic query-to-indexed-view matching. Reference the indexed view by name and include the NOEXPAND hint to have the query processor use the indexed view in all other editions.
• The cost of using the indexed view may exceed the cost of getting the data from the base tables, or the query is so simple that a query against the base tables is fast and easy to find. This often happens when the indexed view is defined on small tables. You can use the NOEXPAND hint if you want to force the query processor to use the indexed view. This may require you to rewrite your query if you don't initially reference the view explicitly. You can get the actual cost of the query with NOEXPAND and compare it to the actual cost of the query plan that doesn't reference the view. If they are close, this may give you confidence that the decision of whether or not to use the indexed view doesn't matter.
• The query optimizer is not matching the query to the indexed view. Double-check the definition of the view and the definition of the query to make sure that a structural match between the two is possible. CASTS, converts, and other expressions that don't logically alter your query result may prevent a match. Also, there are limits to the expression normalization and equivalence and subsumption testing that SQL Server performs. It may not be able to show that some equivalent expressions are the same, or that one expression that is logically subsumed by the other is really subsumed, so it may miss a match.

Q. I update my data warehouse once a week. Indexed views speed up my queries a lot during the week, but slow down the weekly update. What should I do?

A. Consider dropping the indexed views before the weekly update, and creating them again afterwards.

Q. My view has duplicates, but I really want to maintain it. What can I do?

A. Consider creating a view that groups by all the columns or expressions in the view you want and adds a COUNT_BIG(*) column. Then create a unique clustered index on the grouping columns. The grouping process ensures uniqueness. This isn't really the same view, but it might satisfy your needs.

Q. I have a view defined on top of another view. SQL Server won't let me index the top-level view. What can I do?

A. Consider expanding the definition of the nested view by hand into the top-level view and then indexing it, indexing the innermost view, or not indexing the view.

Q. Why do indexed views have to be defined WITH SCHEMABINDING?

A. So that both of the following conditions are met:

• All objects the view references are unambiguously identified using schemaname.objectname, regardless of which user is accessing the view.
• The objects referred to in the view definition can't be altered in a way that would make the view definition illegal or force SQL Server to re-create the index on the view.

Q. Why can't I use OUTER JOIN in an indexed view?

A. Rows can logically disappear from an indexed view based on OUTER JOIN when you insert data into a base table. This makes incrementally updating OUTER JOIN views relatively complex to implement, and the performance of the implementation would be slower than for views based on standard (INNER) JOIN.
 

Indexed views can substantially improve query performance when used appropriately. An indexed view is persistently stored, meaning that the data can be accessed directly without the need to execute the query that defines the view. This is particularly useful for storing precomputed aggregate data.

Good schema design requires balancing the benefits of indexed views with their costs. Indexed views require additional storage space, and updating the base tables on which a view is defined may require updating the indexed view.

Partition-aligned indexed views extend the usefulness of indexed views to scenarios involving partitioned tables. When large tables are partitioned and data is switched in and out of the partitions, partition-aligned indexed views allow you to maintain the indexed views during switching without incurring additional maintenance cost.

Restriction:

• The user that executes CREATE INDEX must be the owner of the view.
• When you create the index, the IGNORE_DUP_KEY option must be set to OFF (the default setting).
• Base tables must have the correct SET options set at the time the table is created or it cannot be referenced by the view with schema binding.
• Tables must be referenced by two-part names, schema.tablename, in the view definition.
• User-defined functions must be created by using the WITH SCHEMABINDING option.
• User-defined functions must be referenced by two-part names, schema.function.
• The view must be created by using the WITH SCHEMABINDING option.
• The view must reference only base tables in the same database, not other views.
• If GROUP BY is present, the VIEW definition must contain COUNT_BIG(*) and must not contain HAVING. These GROUP BY restrictions are applicable only to the indexed view definition. A query can use an indexed view in its execution plan even if it does not satisfy these GROUP BY restrictions.
• 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.
• If the view definition contains a GROUP BY clause, the key of the unique clustered index can reference only the columns specified in the GROUP BY clause.
• The view definition must not contain the following:
• Count(*)
• MIN, MAX
• DISTINCT
• TOP
• SUM on nullable expression
• UNION
• Subquery
• self-join
• Derived table
• ROWSET function
• STDEV, VARIANCE, AVE
• float*, text, ntext, or image columns
• full-text predicates (CONTAIN, FREETEXT)
• CLR user-defined aggregate function

 The following steps are required to create an indexed view and are critical to the successful implementation of the indexed view:

1. Verify the SET options are correct for all existing tables that will be referenced in the view.
2. Verify the SET options for the session are set correctly before creating any new tables and the view.
3. Verify the view definition is deterministic.
4. Create the view by using the WITH SCHEMABINDING option.
5. Create the unique clustered index on the view.

reference;
sql 2008: http://msdn.microsoft.com/en-us/library/dd171921(v=sql.100).aspx
sql 2012: http://msdn.microsoft.com/en-us/library/ms191432.aspx