SQL Server indexed view matching of views with joins not working

Question

Does anyone have experience of when SQL Servr 2008 R2 is able to automatically match indexed view (also known as materialized views) that contain joins to a query?

for example the view

select dbo.Orders.Date, dbo.OrderDetails.ProductID
from dbo.OrderDetails
join dbo.Orders on dbo.OrderDetails.OrderID = dbo.Orders.ID

cannot be automatically matched to the same exact query. When I select directly from this view with (noexpand) I actually get a much faster query plan that does a scan on the clustered index of the indexed view. Can I get SQL Server to do this matching automatically? I have quite a few queries and views and I do not want to reference the indexed view manually each time because I am using an OR mapper.

I am on enterprise edition of SQL Server 2008 R2.

Edit: I found the solution. SQL Server 2008 R2 does not match indexed views with more than 2 joins automatically. Probably it would slow down the optimization process too much.

Answer 1

I'm a little fuzzy on exactly what your question is; but I think this will give you what you want:

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

There are a lot of strange, arbitrary-seeming conditions that limit when SQL Server will use a view index in a query. This page documents them for SQL Server 2008.