SQL Indexed View limitations

SQL Server Indexed Views remain a powerful technique for improving sql query performance , if utilised in the right context. The most effective use is to provide a pre-aggregated  or pre-calculated view of the base data. In other words, users are able to query an aggregation of data without having to process the complex JOINS and aggregates when executing a request.

For example, in a CRM , a daily batch job may pump data into the warehouse. A workflow may run which pre-aggregates the data into an indexed view. The pre-aggregation process may take 15 minutes, but the user does not have to incur the execution time cost of all the JOINS and filters. They simply query the aggregated view.An example of speeding up a query response : Use SQL Materialized view to eliminate DISTINCT and speed up response time

Based on this description , you may think Indexed Views are the answer to all your sql dreams. But they do require planning – as anyone who has attempted to convert existing standard Views into Indexed Views. It is also important to understand why you want to use the Indexed View. What problem are you trying to solve? Are you creating further problems by resorting to Indexed Views and masking bad database design?

When analysing the possibility of using Indexed Views there are a number of limitations. Indexed Views cannot do everything. These are some limitations I’ve experienced recently in a project to convert a number of standard Views into Indexed Views.

1) The Indexed View definition can’t reference other views, or tables in other databases.

2) The Indexed View definition can’t have COUNT, MIN, MAX, TOP, outer joins, and some  other keywords. Check this MSDN link for a full list Indexed View Limitations

3) You can’t modify the underlying tables and columns. The view is created with the WITH SCHEMABINDING option. SHEMABINDING  means that as long as that schemabound object exists as a schemabound object  you are limited in changes that can be made to the tables that it refers to.

4) Predicting the sql query optimizer is not an exact science. SQL Server Enterprise Edition automatically considers the unique clustered index as an option for a query – but if it finds a  more suitable index, that will be used. You could force the sql optimizer to use the index through the WITH NOEXPAND hint – but using Hints should come with a health warning.

5) Using OLTP with indexed views may create performance problems. In a database system with high levels of  insert, updates and deletes, this can become a bottleneck. Every data change that occurs in the base table is reflected into the Indexed View.  If you want to see for  yourself, check the Execution Plan. Read more on architecting write-intensive databases

Read More on Msg 5075 and Query Optimizer

MATERIALIZED VIEW

Msg 5075 - The database collation cannot be changed if a schema ...

SQL Server - Why does Query Optimizer choose a Clustered Index Scan?