How does SQL Server treat indexes on a table behind a view?

Question

So I'm trying to understand how SQL Server makes use of indexes on tables behind views. Here's the scenario: Table A has a composite clustered index on fields 1 & 2 and a nonclustered index on fields 3 & 4.

View A is written against Table A to filter out additional fields, but fields 1-4 are part of the view. So we write a query that joins the view to another table on the nonclustered index fields.

The resulting query plan hits Table A with a clustered index scan (instead of the expected nonclustered index seek). However, if we replace the view in the FROM clause with the table, the query plan then hits the nonclustered index and we get the index seek we expected.

Shouldn't the SQL engine make use of the index on the table the view is constructed on? Since it doesn't, why not?

Answer 1

When you're thinking of non-materialized views and optimizations -- think of them like this:

The engine is "cutting and pasting" the view text into every query you perform.

OK, that's not exactly 100% true, but it's probably the most helpful way to think of what to expect in terms of performance.

Views can be tricky, though. People tend to think that just because a column is in a view, that it means something significant when it comes to query performance. The truth is, if the query which uses your view doesn't include a set of columns, it can be "optimized away". So if you were to SELECT every column from your base tables in your view, and then you were to only select one or two columns when you actually use the view, the query will be optimized considering only those two columns you select.

Another consequence of this is that you can use views to very aggressively flatten out table structures. So let's say for example I have the following schema:

Widget
-------
ID (UNIQUE)
Name
Price
WidgetTypeID (FK to WidgetType.ID)

WidgetType
----------
ID (UNIQUE)
Name

vw_Widgets
----------
SELECT w.ID, w.Name, w.Price, w.WidgetTypeID, wt.Name AS TypeName
FROM Widgets w
LEFT JOIN WidgetType wt
   ON wt.ID = w.WidgetTypeID;

Note the LEFT JOIN in the view definition. If you were to simply SELECT Name, Price FROM vw_Widgets, you'd notice that WidgetType wasn't even involved in the query plan! It's completely optimized away! This works with LEFT JOINS across unique columns because the optimizer knows that since WidgetType's ID is UNIQUE, it won't generate any duplicate rows from the join. And since there's a FK, you know that you can leave the join as a LEFT join because you'll always have a corresponding row.

So the moral of the story here with views is that the columns you select at the end of the day are the ones that matter, not the ones in the view. Views aren't optimized when they're created -- they're optimized when they're used.

Your question isn't really about views

Your question is actually more generic -- why can't you use the NC index? I can't tell you really because I can't see your schema or your specific query, but suffice it to say that at a certain point, the optimizer sees that the cost of looking up the additional fields outweighs what it would have cost to scan the table (because seeks are expensive) and ignores your nonclustered index.