Indexed Views

• Implementing indexed views.

Indexed views are a new concept introduced in SQL Server 2000. They allow view resultsets to be stored in the database's physical storage after an index is created. In contrast to this, in a non-indexed view, the view is activated at run time and the resultset is dynamically built based on logic needed to create output from the under-lying base tables. Any computations , such as joins or aggregations, are done during query execution for each query referencing the view, ultimately leading in degraded performance. Indexed views and other views are covered in full in Chapter 10.

A view can be thought of as a virtual table or a stored query. The results of using a view are not permanently stored in the database. Defining clustered indexes on views enables the resultsets of the view to be stored in the database, thereby reducing the overhead of dynamically building the resultset. This means that indexed views can be treated like any other table because additional nonclustered indexes can be added. An indexed view can be created using the CREATE INDEX or CREATE VIEW statements, which is done when data is occasionally updated. Some of the benefits of indexed views are as follows :

• They increase performance of queries that use joins and aggregates to process numerous rows.

• Data residing in an indexed view is automatically updated when a base table gets modified.

• Views that aggregate and join data are improved because when an index is created on a view, the view is executed and the resultset is stored in the database.

• The first time you create an index on a view, it has to be of type unique clustered. After this has been created, additional nonclustered indexes can be created.

Although benefits can be had from applying an index to a view, there are many considerations to be made that may affect other applications as well.

Indexed View Requirements and Restrictions

NOTE

Index View Optimizer Restrictions The optimizer uses indexed views in only the Enterprise edition, although they can only be created in both the SQL Server 2000 Professional and Enterprise editions.

Some prerequisites need to be met before you create an indexed view, as follows:

• The first index on a view must be a clustered index.

• Non-deterministic functions are not allowed in the view's definition.

• The view must be created using the WITH SCHEMABINDING (which prevents the dropping and altering of tables participating in the indexed view).

• Session-level settings must be modified as shown in Table 10.1.

  NOTE

  Non-Deterministic Functions Are Not Allowed on Indexed Views Deterministic functions are functions that return the same result each time they are called. ABS is a deterministic function because it always returns to the same outcome each time a new argument is supplied. GETDATE , on the other hand, is non-deterministic because it results in a new value each time it is evaluated.

  
  

  Table 10.1. Mandatory Settings

  Session Level Setting	Value
  ANSI_NULLS	ON
  ANSI_WARNINGS	ON
  ARITHABORT	ON
  CONCAT_NULL_YEILDS_NULL	ON
  QUOTED_IDENTIFIERS	ON
  ANSI_PADDING	ON
  NUMERIC_ROUNDABORT	OFF

• Indexed Views' definitions cannot use the following:

  ORDER BY

  COMPUTE or COMPUTE BY

  TOP

  Text , ntext , or image columns

  DISTINCT

  MIN , MAX , COUNT , STDEV , VARIANCE , AVG

  A derived table

  A rowset function

  Another view

  UNION

  Sub-queries, outer joins, self joins

  Full-text predicates such as CONTAIN or FREETEXT

NOTE

Switching Session-Level Settings One way of turning session settings on or off is to use the session property statement. For example, to switch the ANSI_WARNINGS setting on, you can run the following query

 IF sessionproperty ('ANSI_WARNINGS') = 0 SET ANSI WARNINGS ON 

These session settings must be on during the creation of the indexed view, as well as for the clients who wish to use the indexed view.

To avoid having problems with indexed views, keep the restrictions in mind, but also be aware that options must also be in place at the time the indexed view is created. When creating indexed views, keep the following points in mind:

• Extra data storage will be needed. The data in the view is permanently stored both in its base table and in the clustered index of the indexed view.

• Keep indexes on view as small as possible. Doing so enables the SQL optimizer to quickly locate row data.

• Indexed views can increase query speed substantially.

• Applications that run repeated queries benefit from indexed views.

• At times, view indexes would be inconvenient because they restrict you from using non-deterministic functions and other important aggregates.

With all this in mind, there is a lot to consider about the specifics of an implementation of indexed views.

Creating Indexed Views

Creating an indexed view is as simple as altering the CREATE INDEX statement. Use the same syntax used in creating table indexes except that the table name is replaced with the view name. Also, some session-level options must be toggled (see the preceding section). Keep in mind that only the view owner is allowed to execute a CREATE INDEX statement on a view. To create an indexed view, go through Step by Step 10.9.

STEP BY STEP

10.9 Creating an Indexed View

1. Open the Query Analyzer by selecting Query Analyzer from the Start menu.

2. Type in the following in the code pane. The first part of the code configures session-level settings. Remember, some settings need to be enabled and disabled before you can create an indexed view. The second part creates a view so that you can create an index. The last part creates a unique clustered index on the view as shown in Figure 10.8. Notice that WITH SCHEMABINDING option has to be specified.

   [View full width]

    
    [View full width] 
    Use Pubs SET ANSI_PADDING,CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS,  ARITHABORT,ANSI_NULLS, QUOTED_IDENTIFIER ON SET NUMERIC_ROUNDABORT OFF GO CREATE   VIEW IndexView WITH SCHEMABINDING AS SELECT Au_Fname + ' ' + Au_Lname,  Au_id FROM Authors WHERE Contract = 1 GO CREATE UNIQUE CLUSTERED Index MyINDEX ON IndexView (au_id) Go 

   Figure 10.8. Creating an indexed view.

   

3. To see your index view, open the Query Analyzer and the Object browser.

4. Expand Pubs and then expand views.

5. Click IndexView and then expand indexes. You should see the index you created listed under Indexes as shown in Figure 10.9.

   Figure 10.9. The newly created index on the view.

   

All indexes on a view are dropped if the view is dropped. All nonclustered indexes on the view are dropped if the clustered index is dropped. Nonclustered indexes can be dropped individually. Dropping the clustered index on the view removes the stored result set, and the optimizer returns to processing the view like a standard view.

REVIEW BREAK: Using Indexed Views

Indexed views work best when the underlying data is static or infrequently updated. The maintenance of an indexed view can be higher than the cost of maintaining a table index. If the underlying data is updated frequently, then the cost of maintaining the indexed view data may outweigh the performance benefits of using the indexed view.

Indexed views are best used in reporting systems with many joins, functions, aggregations, and other calculations. Any time data is to be summarized, grouped, or otherwise analyzed , indexed views will be of benefit. Indexed views are best when there are many or very expensive selects compared to the number of inserts , updates, and deletes.