The database must always expand nonindexed views into their underlying SELECT statement and access data only from the base table.
Note that if you're accessing the underlying table in your FROM clause, the OPTION (EXPAND VIEWS) hint will keep SQL Server from using an indexed view on that table, but SQL Server 2000 doesn't have a hint to let you do the opposite. You can't force the database to use an indexed view without referencing that view in your query. The optimizer decides whether the view's query tree matches the submitted query's query tree, then determines which is more cost-effective: using the indexed view or the base table. In addition, the SQL Server 2000 optimizer might decide that using the base table is cheap enough and won't even bother determining a cost for using the indexed view.
As I noted earlier, you can force the optimizer to use the index on the view by referencing the indexed view in your FROM clause and using the NOEXPAND hint. However, you can't force the optimizer to use the index on the view by using the INDEX hint, which tells SQL Server to use a particular index. If you use INDEX=1 to try to force the optimizer to use the clustered index, SQL Server will use the clustered index on the base table instead. Even if you specify the name of the clustered index on the view in the INDEX hint, SQL Server will still use the clustered index on the base table. Unless you use the NOEXPAND hint, SQL Server will replace the view with its underlying definition by the time the optimizer starts processing the query, and the optimizer won't see the view.
For testing purposes, you can force SQL Server not to use your indexed views by simply changing a required SET option to the wrong value. For example, setting ANSI_NULLS OFF will disallow indexed views.
Relaxing the Restrictions
The laundry list of syntax restrictions for the CREATE VIEW statement applies only to creating the indexed view. When selecting from the view, you can use any valid SQL terms; the optimizer will then decide whether to include the indexed view in the query execution plan.
Not being able to include the average (AVG) aggregate in your view definition might seem like a major inconvenience. But keep in mind that you can still use SUM, and you must use the COUNT_BIG aggregate if you have any other aggregates, so you can compute the average yourself.
For example, the Product_Totals indexed view contains a total_qty and a number column for each productid. You just divide the former by the latter to get the average:
SELECT productid, average_qty = total_qty/number
FROM Product_Totals
The query execution plan and the STATISTICS IO values show that the optimizer used the indexed view to process this query.
To verify that this is the same result you would get if you used the AVG aggregate, run the following query:
SELECT productid, average_qty = AVG(Quantity)
FROM dbo."order details"
GROUP BY productid
Not only does this second query yield the same results as the first, but the optimizer chose to use the indexed view to process the second query also so that the database didn't have to compute the sum and the count during execution. Not all applications and queries, however, will benefit from indexed views. For tips about which applications and queries will benefit most from this new feature, see the sidebar "The Best Views for Indexes," at left.
Managing and Tuning Indexed Views
In many ways, you manage indexed views like you manage any other view. The sp_help procedure tells you which columns are part of the view, and sp_helptext shows you the view's definition, unless you create the view WITH ENCRYPTION.
DROP VIEW drops an indexed view as easily as it drops a non-indexed view. And if you drop the clustered index on a view, SQL Server automatically drops all nonclustered indexes. You can use ALTER VIEW to change an indexed view's definition, but be aware that if you alter an indexed view, all indexes on that view disappear. You have to rebuild the clustered index to make the view an indexed view again. As a precaution, make sure you save all your index definitions in script files.
SQL Server 2000 extends the Index Tuning Wizard (ITW), introduced in SQL Server 7.0, to recommend not only indexes on tables but to optionally build views and indexes on views. By default, this option is turned on in beta 1, but you can turn this option off if you don't want the ITW to consider indexed views.
If you do let the ITW define indexed views for you, be careful. Because the ITW provides correct values for all required SET options when it creates the indexed views, the views will always succeed during hypothetical analysis. However, if your SET options have different values, your applications won't be able to use the indexed views, and inserts, deletes, and updates on tables included in the indexed view definitions will fail.
Power Up Indexed Views
To get the most out of indexed views,
- analyze your workload so that you're familiar with the types of queries your users run.
- enable the proper SET options before creating any tables used in the view definition.
- use the OBJECTPROPERTY function's 'IsIndexable' option to make sure you can index the view.
- make sure the first index on the view is unique and clustered (other indexes don't have to be unique or clustered).
And remember that you don't need to change your applications or queries to take advantage of any indexed views you build. The optimizer determines whether an indexed view is available and useful, and your applications need never change.
End of Article
Prev. page
1
2
3
4
[5]
next page -->
