Indexed Views


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:

  • The ANSI_NULLS and QUOTED_IDENTIFIER options must be enabled when the view is created, and ANSI_NULLS must have been enabled when the tables referenced by the view were created.

  • The view can only reference tables and not any other views, and all tables must be in the same database as the view and be owned by the same user creating the view.

  • The SCHEMABINDING option must be specified when the view is created.

  • Only two part names ( owner.objectname ) can be specified for all tables and user-defined functions referenced in the view.

  • Any functions referenced in the view must be deterministic. A deterministic function is one that returns the same result every time it is called with the same set of input parameters (for information on which functions in SQL Server are nondeterministic, see Chapter 26, "Using Transact-SQL in SQL Server 2000").

  • All column names must be explicitly specified in the view ( SELECT * is not allowed), and cannot be specified more than once.

  • The view cannot contain any text, ntext, or image columns .

  • The view cannot include data from a derived table, a rowset function, a UNION , a subquery, an outer join, or a self join.

  • The select statement in the view cannot contain the TOP , ORDER BY , DISTINCT , COMPUTE , COMPUTE BY , HAVING , CUBE , or ROLLUP clauses.

  • The select statement in the view can only contain aggregate functions if the GROUP BY clause is specified, and cannot contain the complex aggregate functions: avg() , min() , max() , stdev() , stdevp() , var() , or varp() .

  • The COUNT(*) function cannot be used, but COUNT_BIG(*) can.

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.

NOTE

Indexed views can be created in any edition of SQL Server 2000, but they will only be considered for query optimization in the Developer and Enterprise editions of SQL Server 2000. In other editions of SQL Server 2000, the optimizer will only consider the indexed view when it is explicitly referenced in the query and the NOEXPAND optimizer hint is specified. For example, to force the optimizer to consider using the sales_Qty_Rollup indexed view in the Standard Edition of SQL Server 2000, you need to execute the query as follows :

 select * from sales_Qty_Rollup WITH (NOEXPAND)     where stor_id between 'B914' and 'B999' 

For more information on how indexed views are used by the query optimizer, see Chapter 35, "Understanding Query Optimization."

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).



Microsoft SQL Server 2000 Unleashed
Microsoft SQL Server 2000 Unleashed (2nd Edition)
ISBN: 0672324679
EAN: 2147483647
Year: 2002
Pages: 503

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net