To make an indexed view, you need to create a unique clustered index on the view. This statement defines a unique clustered index, Product_Totals, for the view:
CREATE UNIQUE CLUSTERED INDEX PV_IDX on Product_Totals(productid)
After you create the index, you can rerun sp_spaceused. You'll receive results similar to those in Figure 1.
Data that comprises the indexed view is persistent, with the indexed view storing the data in the clustered index's leaf level. You could construct something similar by using temporary tables to store the data you're interested in. But a temporary table is static and doesn't reflect changes to underlying data. In contrast, SQL Server automatically maintains indexed views, updating information stored in the clustered index whenever anyone changes data that affects the view.
After you create the unique clustered index, you can create multiple nonclustered indexes on the view. By using the ObjectProperty function's 'IsIndexed' argument, you can determine whether a view is indexed. For the Total_Products indexed view, the following statement returns a 1 (is indexed):
SELECT ObjectProperty(object_id ('Product_Totals'), 'IsIndexed')
Note that at beta 1, executing system stored procedure sp_help on the view doesn't report the view as indexed and doesn't show any of the indexes. However, running system stored procedure sp_helpindex on the view returns complete information about any indexes on the view.
Using Indexed Views
One of indexed views' most valuable benefits is that your queries don't have to directly reference a view to use the index on the view. Consider the Product_Totals indexed view. Suppose you issue the following SELECT statement:
SELECT productid, total_qty = sum(Quantity)
FROM dbo."order details"
GROUP BY productid
SQL Server's query optimizer realizes the precomputed sums of all the Quantity values for each productid are already available in the index for the Product_ Totals view. The optimizer will evaluate the cost of using that indexed view in processing the query. But just because you have an indexed view doesn't mean the query optimizer will always choose it for the query's execution plan. In fact, even if you reference the indexed view directly in the FROM clause, the optimizer might decide to directly access the base table instead.
To determine whether the optimizer is using the indexed view, you can look at the query plan in the Query Analyzer. The graphical Estimated Execution Plan in Screen 1 shows that the query is using the clustered index on the view.
You can compare the cost of using an indexed view's precomputed values with the cost of directly accessing the base order details table. A new SQL Server 2000 query hint, OPTION (EXPAND VIEWS), forces SQL Server to expand all indexed views into their underlying SELECT statements so that the optimizer won't consider indexes on the views. To compare the cost of accessing the same data with and without the indexed view, execute SET STATISTICS IO ON, run the code in Listing 2 (which executes one SELECT that uses the indexed view and one that uses EXPAND VIEWS), then compare the values returned for logical reads. When I ran this code, I received a value of 2 logical reads when the system used the indexed view and 19 logical reads when I forced SQL Server to expand the view.
A second new index hint, NOEXPAND, does the opposite of EXPAND VIEWS. You use NOEXPAND in your query's FROM clause to force SQL Server to use the indexed view, but only if you've referenced the view in your FROM statement. Listing 3 shows an indexed view on Northwind's Orders table. A simple query selecting from this view doesn't use the index on this view, as you can see if you display the query's execution plan. However, using the hint WITH (NOEXPAND) forces the query optimizer to consider only the view—and indexes on that view—in the execution plan. The optimizer won't consider the base table or its indexes. If you use the WITH (NOEXPAND) hint with a view that isn't indexed, you'll get the following
error:
Server: Msg 8171, Level 16, State 2, Line 1
Hint 'noexpand' on object 'MyRegularView' is invalid.
Prev. page
1
2
3
[4]
5
next page 
