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

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]