SQL Server indexed views

Question

I am trying to create an indexed view in SQL Server and I was wondering If I needed to index the view columns.

I am asking this because the view is composed of a tables that already have the columns indexed.

So if TABLE1 has the column FOO already indexed as a non clustered index, do I have to add an index for the column FOO to the newly created view for SQL Server to use the index?

Or will SQL Server know to use the index in TABLE1 when searching the view?

The view looks like this

CREATE VIEW [dbo].[v_eventActivity] 
WITH SCHEMABINDING 
AS 
  SELECT ea.id, 
         e.eventID, 
         e.name, 
         ea.userID, 
         ea.activityTypeID, 
         ea.timeStamp, 
         ea.visitDuration 
  FROM   dbo.table1 e, 
         dbo.table2 ea 
  WHERE  e.eventID = ea.eventID 

I am going to be searching on all of those columns together.

As stated before, table1 and table2 all have already have those columns indexed.

Answer 1

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.

Answer 2

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 ixFoo
ON [test].[TestTable] (foo)

CREATE VIEW [test].[TestTableView] WITH SCHEMABINDING
AS
    SELECT
        t.id,
        t.foo
    FROM [test].[TestTable] t
GO

CREATE UNIQUE CLUSTERED INDEX ixFooId
ON [test].[TestTableView] (id)

CREATE NONCLUSTERED INDEX ixFooView
ON [test].[TestTableView] (foo)

Here's the execution plan for three separate queries:

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

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

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