SQL Server indexed view matching of views with joins not working

Posted by usr on Stack Overflow See other posts from Stack Overflow or by usr
Published on 2010-05-22T08:22:30Z Indexed on 2010/05/22 8:30 UTC
Read the original article Hit count: 244

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 ith (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...

I am on enterprise edition of SQL Server 2008 R2.

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about sql-server-2008