Sql-server – How many LEFT JOINS make a VIEW unusable

execution-plansql serversql-server-2008-r2view

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.

Best Answer

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

Related Question