Used properly, indexed views can be a magnificent way of improving performance and providing greater ease for both users and developers while still maintaining a fully normalized and constrained database. However, to fully realize the benefits of the indexed view, the execution plan must actually make use of it.

When a nonindexed view or a view whose index will not be used in the execution plan is referenced in a query, the optimizer considers the select statement the view represents the same way it would consider a subquery. In a way, this is very much like using the view as a macro. It saves the user or developer from having to type, or even be fully aware of, the exact contents of the view, but the optimizer looks at it as though the developer had typed it in.

When dealing with a view with an index that will be used, the optimizer can go to materialized data the view represents instead. In most cases, this is can be much more efficient than expanding the view, especially when the view includes multiple joins or complex where statements within it. So, while some exceptions do exist, it is generally desirable to ensure that the index is used.

According to Books Online, the optimizer for SQL Server 2005 Enterprise Edition will intelligently decide when it is best to use or ignore indexes on views. Not only that, it has the ability to consider using the indexes on views if they would by applicable to the base tables as well. That only applies to Enterprise Edition, to quote directly from the Resolving Indexes on Views article on MSDN:

To use an indexed view in all other editions, the NOEXPAND table hint must be used.

In short, indexed views in every version but Enterprise will only be used to their full potential if the developers and users carefully ensure that they appropriately use the NOEXPAND hint. Also, applying the NOEXPAND hint to a view which does not have an index will generate an error. It can be illuminative to see an example.

First, some test data is needed.:

/* Generate 3 test tables for test data.
The technique used get the initial tally table described 
by Jeff Moden in: The "Numbers" or "Tally" Table
*//* Set proper conditions
as described by MSDN in Creating Indexed Views*/
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO
/* Create the tally table to be used. */
IF OBJECT_ID('dbo.Tally') IS NOT NULL
 DROP TABLE dbo.Tally
SELECT TOP 8000
 IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM MASTER.dbo.SysColumns sc1,
 MASTER.dbo.SysColumns sc2
--===== Conditionally drop
IF OBJECT_ID('dbo.vwTestView') IS NOT NULL
 DROP VIEW dbo.vwTestView
IF OBJECT_ID('dbo.a') IS NOT NULL
 DROP TABLE dbo.a

IF OBJECT_ID('dbo.b') IS NOT NULL
 DROP TABLE dbo.b 
IF OBJECT_ID('dbo.c') IS NOT NULL
 DROP TABLE dbo.c 
SELECT TOP 10000
 N AS id,
 N AS JoinB,
 'a' AS ADATA
INTO
 dbo.a
FROM
 dbo.Tally
SELECT TOP 10000
 N AS id,
 N AS JoinC,
 'b' AS BDATA
INTO
 dbo.b
FROM
 dbo.Tally

SELECT TOP 10000
 N AS id,
 'c' AS CDATA
INTO
 dbo.c
FROM
 dbo.Tally 

/* Add primary keys for performance */ 
GO
ALTER TABLE dbo.a
 ADD CONSTRAINT PK_Tally_a
 PRIMARY KEY CLUSTERED (id)
ALTER TABLE dbo.b
 ADD CONSTRAINT PK_Tally_b
 PRIMARY KEY CLUSTERED (id) 

ALTER TABLE dbo.c
 ADD CONSTRAINT PK_Tally_c
 PRIMARY KEY CLUSTERED (id) 


/* Create the view */
GO 

CREATE VIEW vwTestView
WITH schemabinding /* Schemabinding is required to create the indexes */
AS
SELECT
 a.ID,
 a.ADATA,
 b.BDATA,
 c.CDATA
FROM
 dbo.a a
 JOIN dbo.b b
 ON b.id = a.JoinB
 JOIN dbo.c c
 ON c.id = b.JoinC

/* Create the index */
GO

CREATE UNIQUE CLUSTERED INDEX [vwTestViewClusteed] ON [dbo].[vwTestView]
(
 [ID] ASC
) 

Then, run the select statements and compare:

SELECT
 *
FROM
 vwTestView

SELECT
 *
FROM
 vwTestView WITH (NOEXPAND) 

And here are the execution plans:

The second plan generated with the NOEXPAND query hint is vastly more efficient than the one without because of the way they are processed. This particular execution plan was generated by SQL Server 2005 Express, but the same results come from Standard Edition as well.

One way to simplify this for developers and users is to use another view to hide the query hint. For instance:

CREATE VIEW vwTestViewX
AS
SELECT
 *
FROM
 dbo.vwTestView WITH (NOEXPAND) 

Then a query against this view will include the NOEXPAND against the other and generate an appropriate execution plan like:

One thing to note with embedding the NOEXPAND hint is that the query will return an error if NOEXPAND is specified and no indexes exist on the view. This should rarely be an issue, but it is worth noting. This is especially true since if a change is made to one of the base tables using SSMS Designer, the Designer will display a confirmation message that the table is referenced with Schemabinding but then readily destroy the index if the confirmation is given. This can lead to indexes being destroyed in development environments very easily.

As long as the optimizer knows to use them, indexed views can provide enormous performance benefits. In the more powerful Enterprise Edition, the optimizer will handle this and there is rarely a reason to explicitly force their use. In other versions, though, the optimized must be explicitly told if they are needed. But, like other query hints, this explicit instruction can be made just once in a view on the indexed view if that is desirable.

REFERENCES
Creating Indexed Views by MSDN ( http://msdn.microsoft.com/en-us/library/ms191432.aspx )
Resolving Indexes on Views by MSDN ( http://msdn.microsoft.com/en-us/library/ms181151.aspx )
View Resolution by MSDN ( http://msdn.microsoft.com/en-us/library/ms190237.aspx )
The "Numbers" or "Tally" Table by Jeff Moden ( http://www.sqlservercentral.com/articles/TSQL/62867/ )

RELATED ARTICLES
What is Denormalization? By Chris Kempster ( http://www.sqlservercentral.com/articles/Advanced/whatisdenormalization/1204/ )

The Myth of Over-Normalization by Tony Davis (
http://www.simple-talk.com/community/blogs/tony_davis/archive/2008/07/21/63094.aspx )