As stated earlier in this chapter, views are simply stored SELECT statements, and as such, have no data storage requirements and incur little if any overhead in the database. However, just when you thought it was safe to come out of the server room, SQL Server 2000 introduces the concept of the indexed view. Just as it sounds, an indexed view is one on which you create an index. To be more precise, an indexed view is one on which there exists a unique clustered index. When a unique clustered index is defined on a view, the resultset generated by the view is materialized and stored in the leaf level of the index as if the clustered index were defined on a table. When the data in the underlying base table(s) changes, the data in the indexed view is updated to reflect the changes, in much the same way that changes to a table are reflected in its indexes. Gone with the indexed view is the concept of the view as a virtual table because it now has associated data storage.
Creating Indexed Views
The syntax to create an indexed view is the same as creating a normal view. However, there are a number of requirements that must be met in order to create an index on a view:
This is a rather extensive list of criteria that must be met for an index to be created on a view. Fortunately, SQL Server provides the OBJECTPROPERTY function to help determine if a view can be indexed. The IsIndexable property can be queried to verify whether index creation would be successful for a view. The following statement checks whether an index can be created on the view high_sales_view ; a value of 1 is returned if the index creation will be allowed:
SELECT OBJECTPROPERTY (object_id ('high_sales_view'),'IsIndexable')
If a value of is returned, then the view doesn't meet the criteria for indexing. Check your view definition against the indexed view requirements, and use ALTER VIEW to adjust the view definition.
Creating an index on a view is just like creating an index on a table, except you specify the view name instead of a table name . The first index on an indexed view must be a unique clustered index. In addition to the unique clustered index, you can create additional nonclustered indexes on indexed views to provide additional query performance. Additional indexes on views might provide more options for the query optimizer to choose from during the optimization process.
The user creating the index must be the owner of the view and the following SET option settings must be in effect when the index is created:
SET ARITHABORT ON SET CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET NUMERIC_ROUNDABORT OFF
The following example creates an indexed view on the high_sales_view table:
create unique clustered index high_sales_UCI on High_Sales_View (orderid, customerid, productid)
Advantages of the Indexed View
There must be some method in this madness of saddling a perfectly good view with data storage, and indeed there is.
The question commonly asked about indexed views is, "If indexes are required, why not just create them on the base table columns?" The answer to this question lies in the types of views on which you might create indexes.
When you create an index on a table, an index entry exists for every row. That applies to a view as well. However, if the view were to contain grouped aggregate values, its resultset could be many times smaller than the actual number of rows in the base tables. When the view's index is used to satisfy a query, not only are there fewer rows to be processed , but the aggregate values are already stored in the index ready to be returned without further calculation. This also applies to views containing a WHERE clause that reduces the number of rows returned.
Queries against views that join multiple tables also can benefit from an index. Rather than costly join operations being performed for each query, the data is already "joined" and stored in the index. Without the indexed view, even if the referenced base table columns were all indexed, the join operation would still have to be performed.
Another advantage is that the view doesn't have to be referenced in the FROM clause for the index to be used by the query optimizer. In the Developer and Enterprise editions of SQL Server, queries referencing the base tables can take advantage of the view's indexes. For example, if a query against a table requests a SUM for a particular column, and that column already has a SUM stored in an indexed view, the query analyzer is smart enough to consider using the value from the indexed view, rather than compute a new value from the table data.
Disadvantages of the Indexed View
The prime disadvantage of the indexed view is the same disadvantage inherent in all indexes; data is stored and modified in more than one location. This can be even more of a drawback for the indexed view because, as explained earlier, one of the indexed view's great advantages is in storing grouped aggregate data. When data is updated in the base tables from which the aggregate is drawn, the value must be recalculated and stored in the indexed view. Also, the scope of a view's index can be larger than that of any single table's index, especially if the view is defined on several large tables.
Therefore, indexed views may be inappropriate for tables in which the data is frequently updated. Because of this additional maintenance overhead, consider creating indexes only on those views where the advantage provided by the improved speed in retrieving the results outweighs the increased maintenance overhead. Also, if the grouped aggregate value of the view has a high cardinality, meaning it does not reduce the row set significantly as opposed to the table, its drawbacks might outweigh its benefits.
With the exception of indexed views on frequently joined, rarely updated tables, the indexed view is usually more appropriate for Decision Support Systems (DSS) than it is for Online Transaction Processing (OLTP).