I have a view which involves LEFT JOINS on 20+ tables to a base table but when querying this view and looking at the execution plans I am getting something totally unexpected.

For example, with the query below I would expect a clustered index seek on the table:

SELECT id FROM dbo.TableName WHERE id = 256

Whereas when I use the view I get the clustered index seek on the table along with 3 nested loops (left outer join).

SELECT id FROM dbo.ViewName WHERE id = 256

Why would this occur? The above is a very simple example but when selecting additional columns from the view more and more unnecessary LEFT JOINS appear in the execution plan.

  • 2
    We'll need the queries and execution plans to answer this. Oct 19, 2014 at 11:57
  • 2
    Related question on SO. Joining 100 tables Oct 19, 2014 at 12:00
  • Can you post a sample view and query from the view that demonstrates the problem?
    – Dan Guzman
    Oct 20, 2014 at 5:37

1 Answer 1


The link provided by Martin Smith to an existing SO question provided me with the answer:

The main restrictions are that foreign key relationships must be based on a single key to contribute to the simplification process


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.