SQL: View against Table - are queries against the View still using Table Indexes?

Question

I'm using a view ("UsersActive") against my table ("Users"). The view has only one filter, it checks if DateTime Users.DeletedOn is NULL; it basically contains all Users that are not deleted.

If I now execute Linq queries against the view instead of the table, will they still use the table indexes or do I need to create special indexes for the view? In my understanding the View is nothing else but a predefined query and should work just as if I was querying this directly:

SELECT * FROM Users WHERE DeletedON = NULL

Is my assumption that the underlying table's indexes will still be used correct?

Answer 1

Most of the time, SQL Server Query Optimizer is smart enough to use the indexes on the base table. You can see this by looking at the query plans.

If you have enterprise edition of the SQL Server you can also use indexed views.

Answer 2

The view if not indexed will be regenerated everytime using the query that defines it. If the table in that query is indexed, the table's indexes will be used.

You can also index your view, in which case the dataset will be committed to physical disk and you'll have two sets of indexes.

Answer 3

Views are pretty totally transparent straight to the underlying SQL statements. It's a good reliable design assumption.

Answer 4

An indexed view is very different from a non-indexed view.

A view can effectively be expanded inline by the engine, so any table indexes which are determined by the optimizer can be used.

An indexed view materializes (and keeps up to date) an index. This is available to users of the view, but still might not be used.

An additional note based on your example - an index might not be as effectively used on NULL criteria as it can be on flag or code columns.

Answer 5

Most likely you need to rewrite your query:

SELECT * FROM Users WHERE DeletedON IS NULL

DeletedON = NULL will not be true even if DeletedON IS NULL