Filtered Index / Views Material Filtered Index vs Indexed Views

Filtered Index Design Guidelines

http://msdn.microsoft.com/en-us/library/cc280372.aspx


this article contains the following :

Indexed Views vs. Filtered Indexes


Filtered indexes have the following advantages over indexed views:

Reduced index maintenance costs. For example, the query processor uses fewer CPU resources to update a filtered index than an indexed view.

Improved plan quality. For example, during query compilation, the query optimizer considers using a filtered index in more situations than the equivalent indexed view.

Online index rebuilds. You can rebuild filtered indexes while they are available for queries. Online index rebuilds are not supported for indexed views. For more information, see the REBUILD option for ALTER INDEX (Transact-SQL).

Non-unique indexes. Filtered indexes can be non-unique, whereas indexed views must be unique.

For the above reasons, we recommend using a filtered index instead of an indexed view when possible. It is possible to use a filtered index instead of an indexed view when the following conditions are met: the view references only one table, queries do not return computed columns, and the view predicate uses simple comparison logic. For example, the following predicate expression is allowed in a view definition but not in filtered indexes, because it contains the LIKE operator.

WHERE StartDate > '20040701' AND ModifiedDate LIKE 'E%'

=======================================================
Scenarios for Using Views
http://msdn.microsoft.com/en-us/library/ms188250.aspx


To Focus on Specific Data
To Simplify Data Manipulation
To Provide Backward Compatibility
To Customize Data
To Export and Import Data
To Combine Partitioned Data Across Servers
=======================================================

How are Views stored in database

The rows of an INDEXED VIEW are stored in the database in the same format as a table. If the query optimizer decides to use an indexed view in a query plan, the indexed view is treated the same way as a base table.

Only the definition of a NONINDEXED VIEW is stored, not the rows of the view. The query optimizer incorporates the logic from the view definition into the execution plan it builds for the SQL statement that references the nonindexed view.