Indexed view

Question

Hi,

I have the following question:

1. I have a base table with an index and a view on top of that table which is actually INNER JOIN with another table and LEFT JOIN with 2 other tables
2. I added an index on the base table which improved performance of a query which used the view
3. User is asking is it possible to add the index on a view itself
4. a. How can I find out if there are already indexed views in our system? I tried RedGate Search tool and tried searching 'WITH SCHEMABINDING' but it seems to return all the views even though the scripts don't show the 'WITH SCHEMABINDING' in the script shown in that tool

Does it make sense to add an index on the view? It seems that adding index on the base table already improved the performance. The table is being updated on a daily basis (~10K rows added every day).

Answer 1

Here is a query for indexed views:

select schema_name(v.schema_id) as schema_name,  
       v.name as view_name,  
       i.name as index_name,  
       m.definition  
from sys.views v  
join sys.indexes i  
     on i.object_id = v.object_id  
     and i.index_id = 1  
     and i.ignore_dup_key = 0  
join sys.sql_modules m  
     on m.object_id = v.object_id  
order by schema_name,  
         view_name;  

However, the only real benefit of indexed views comes if you have indexes on calculated values in the views. If you are doing straight selects, you can usually just create the indexes on the tables to improve the performance.

Answer 2

Does it make sense to add an index on the view?

Not really or only if you are using Enterprise Edition, which can benefit on such index.
The best bet is always to create the index on base tables, so every query could use the index, not only queries on the view.

Answer 3

If your view has left joins, then you cannot make it an indexed view. There is a long list of restrictions on what an indexed view cannot contain, you can find it at create-indexed-views . Scroll down to the "Additional Requirements" section.

Tom