CODEWITHSUNDEEP
sql-serversql-server-2008query-optimizationmaterialized-viewsindexed-view
usr
usr

Reputation: 171178

SQL Server indexed view matching of views with joins not working

Does anyone have experience of when SQL Server 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 automatically be 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.

Edit 2: Reviewing this 2 years after the question was created by me, I don't think my conclusion was correct. Materialized view matching is a very fragile process with no clear rules that I could find over the years.

Certainly, the following play a role:

Upvotes: 3

Views: 650

Answers (1)

RMorrisey
RMorrisey

Reputation: 7739

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.

Upvotes: 2

Related Questions