SQL Server indexed view matching of views with joins not working
- by usr
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.
