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.