SQL Server indexed views

The view will simply utilize the table index unless the NOEXPAND hint is supplied (documentation here).

You can test this yourself as follows:

CREATE TABLE [test].[TestTable] (    id INT IDENTITY PRIMARY KEY,    foo INT)CREATE NONCLUSTERED INDEX ixFooON [test].[TestTable] (foo)CREATE VIEW [test].[TestTableView] WITH SCHEMABINDINGAS    SELECT        t.id,        t.foo    FROM [test].[TestTable] tGOCREATE UNIQUE CLUSTERED INDEX ixFooIdON [test].[TestTableView] (id)CREATE NONCLUSTERED INDEX ixFooViewON [test].[TestTableView] (foo)

Here's the execution plan for three separate queries:

SELECT    t.[id],    t.[foo]FROM [test].[TestTable] tORDER BY t.[foo]

SELECT    v.[id],    v.[foo]FROM [test].[TestTableView] vORDER BY v.[foo]

SELECT    v.[id],    v.[foo]FROM [test].[TestTableView] v WITH (NOEXPAND)ORDER BY v.[foo]

Indexed views in SQL Server are, as near as makes no difference, what's called materialized view elsewhere. If your view has an underlying query that uses indexes defined on the base tables, the select on the view will use the index as well, that's not what an indexed views are about.

If you are using the view quite often and performance matters, you can choose to give up some more disk space (and cpu time) and create a unique clustered index on the view, thus enable even faster queries on the view, because SQL Server will not have to go back to the base table or tables, and get everything it needs from the index of the view.

Have a look here.