Improving Performance with SQL Server 2000 Indexed Views

By Gail Erickson, author

Lubor Kollar, contributor

Jason Ward, contributor

Microsoft Corporation

September 2000

Summary: This document describes the new indexed views capability of SQL Server 2000 Enterprise Edition. Indexed views are explained and specific scenarios in which they may provide performance improvements are discussed.

On This Page

What Is an Indexed View?
Benefits of Using Indexed Views
Design Considerations
Creating Indexed Views
Examples
For More Information

What Is an Indexed View?

For many years, Microsoft® SQL Server™ has supported the ability to create virtual tables known as views. Historically, these views served two main purposes:

1. To provide a security mechanism that restricts users to a certain subset of data in one or more base tables.

2. To provide a mechanism that allows developers to customize how users can logically view the data stored in base tables.

With SQL Server 2000, the functionality of SQL Server views has been expanded to provide system performance benefits. It is possible to create a unique clustered index on a view, as well as nonclustered indexes, to improve data access performance on the most complex queries. In SQL Server 2000, a view that has a unique clustered index is referred to as an indexed view.

Note: Indexed views are a feature of SQL Server 2000 Enterprise Edition and SQL Server 2000 Developer Edition only.

From the Database Management System (DBMS) perspective, a view is a description of the data (a meta data). When a typical view is created, the meta data is defined by encapsulating a SELECT statement that defines a result set to be represented as a virtual table. When a view is referenced in the FROM clause of another query, this meta data is retrieved from the system catalog and expanded in place of the view's reference. After view expansion, the query optimizer compiles a single execution plan for the executing query.

In the case of a nonindexed view, the view is materialized at run time. Any computations, such as joins or aggregations, are done during query execution for each query referencing the view. (The view does not always need to be fully materialized. The query can contain additional predicates, joins, or aggregations that can be applied to the tables and views referenced in the view.) After a unique clustered index is created on the view, the view's result set is materialized immediately and persisted in physical storage in the database, saving the overhead of performing this costly operation at execution time.

The indexed view can be used in a query execution in two ways. The query can reference the indexed view directly, or, more importantly, the query optimizer can select the view if it determines that the view can be substituted for some or all of the query and it is the low-cost query plan. In the second case, the indexed view is used instead of the underlying tables and their ordinary indexes. The view does not need to be referenced in the query for the query optimizer to use it during query execution. This allows existing applications to benefit from the newly created indexed views without changing those applications.

Performance Gains from Indexed Views

Using indexes to improve query performance is not a new concept, however, indexed views provide additional performance benefits that cannot be achieved using standard indexes. Indexed views can increase query performance in the following ways:

• Aggregations can be precomputed and stored in the index to minimize expensive computations during query execution.

• Tables can be prejoined and the resulting data set stored.

• Combinations of joins or aggregations can be stored.

The graph below demonstrates the typical performance increases that can be achieved when the query optimizer uses an indexed view. The represented queries varied in complexity (for example, the number of aggregate calculations, the number of tables used, or the number of predicates) and included large multi-million row tables from a real production environment.

Figure 1: Typical performance increases that can be achieved when the query optimizer uses an indexed view

Using Secondary Indexes on Views

Secondary, nonclustered indexes on views can provide additional query performance. Similar to secondary indexes on tables, secondary indexes on views may provide more options for the query optimizer to choose from during the compilation process. For example, if the query includes columns not covered by the clustered index, the optimizer can choose one or more secondary indexes in the plan and avoid a time-consuming full scan of the indexed view or base tables.

Adding indexes to the schema increases the overhead on the database because the indexes will require on-going maintenance. Careful consideration should be given to finding the right balance of indexes and maintenance overhead.

Benefits of Using Indexed Views

Analyze your database workload before implementing indexed views. Use your knowledge of the queries as well as various tools (for example, SQL Profiler) to identify the queries that can benefit from indexed views. Frequently occurring aggregations and joins are the best candidates for indexed views.

Not all queries will benefit from indexed views. Similar to ordinary indexes, if the indexed views are not used, there is no benefit. In this case, not only are performance gains not realized, but the additional cost of disk space, maintenance, and optimization is incurred. However, when indexed views are used, they can provide significant improvements (by orders of magnitude) in data access. This is because the query optimizer uses the precomputed results stored in the indexed view, substantially reducing the cost of the query execution.

The query optimizer considers indexed views only for queries with nontrivial cost. This avoids situations where trying to match various indexed views during the query optimization costs more than the savings achieved by the indexed view usage. Indexed views are rarely used in queries with a cost of less than 1.

Applications that benefit from the implementation of indexed views include:

• Decision support workloads

• Data marts

• Online analytical processing (OLAP) stores and sources

• Data mining workloads

From the query type and pattern point of view, the benefiting applications can be characterized as those containing:

• Joins and aggregations of large tables

• Repeated patterns of queries

• Repeated aggregations on the same or overlapping sets of columns

• Repeated joins of the same tables on the same keys

• Combinations of the above

On the contrary, online transaction processing (OLTP) systems with many writes, or databases with frequent updates, may not be able to take advantage of the indexed views because of the increased maintenance cost associated with updating both the view and underlying base tables.

How the Query Optimizer Uses Indexed Views

The SQL Server query optimizer automatically determines when an indexed view can be used for a given query execution. The view does not need to be referenced directly in the query for the optimizer to use it in the query execution plan. Therefore, existing applications may take advantage of the indexed views without any changes to the application itself. Only the indexed views have to be created.

Optimizer considerations

The query optimizer considers several conditions to determine if an indexed view can cover a portion or the entire query. These conditions correspond to a single FROM clause in the query and consist of the following:

• The tables in the query FROM clause must be a superset of the tables in the indexed view FROM clause.

• The join conditions in the query must be a superset of the join conditions in the view.

• The aggregate columns in the query must be a subset of the aggregate columns in the view.

• All expressions in the query select list must be derivable from the view select list or from the tables not included in the view definition.

• The query search condition predicates must be a superset of the search condition predicates in the view definition. Each conjunct in the view search predicate must appear in the same form as a conjunct in the query search predicate.

  All columns in the query search condition predicates that belong to tables in the view definition must appear in one or more of the following:

  • The same predicate in the view definition.

  • A GROUP BY list.

  • The view select list if there is no GROUP BY.

If the query contains more than one FROM clause (subqueries, derived tables, UNION), the optimizer may select several indexed views to manage a query with multiple FROM clauses.

Note: There are exceptional situations when the optimizer may collapse two FROM clauses into one (subquery to join, or derived table to join transformation). If that happens, the indexed view substitution may cover more than one FROM clause in the original query.

Example queries demonstrating these conditions are presented at the end of this document. Allowing the query optimizer to determine which indexes, if any, to use in the query execution plan is the recommended best practice.

Using the NOEXPAND option

The NOEXPAND option forces the query optimizer to treat the view like an ordinary table with a clustered index. In this case, the indexed view must be referenced directly in the FROM clause. For example:

SELECT Column1, Column2, ... FROM Table1, View1 WITH (NOEXPAND)WHERE ...

Using the EXPAND VIEWS options

Alternatively, the user can explicitly exclude indexed views from consideration by using the EXPAND VIEWS option at the end of the query. For example:

SELECT Column1, Column2, ... 
FROM Table1, View1 
WHERE ...OPTION (EXPAND VIEWS)

When this option is used, the query optimizer ignores all view indexes when estimating the low-cost method of covering the columns referenced in the query.

Design Considerations

Identifying an appropriate set of indexes for a database system can be complex. While there are numerous possibilities to consider when designing ordinary indexes, adding indexed views to the schema dramatically increases the complexity of the design and the potential results. For example, indexed views can be used on:

• Any subset of tables referenced in the query.

• Any subset of the conditions in the query for that subset of tables.

• Grouping columns.

• Aggregate functions, such as SUM.

Indexes on tables and indexed views should be designed concurrently to obtain the best results from each construct. Because both indexes and indexed views may be useful for a given query, designing them separately can lead to redundant recommendations that incur high storage and maintenance overhead. While tuning the physical design of a database, trade offs must be made between the performance requirements of a diverse set of queries and updates that the database system must support. Therefore, identifying an appropriate physical design for indexed views is a challenging task, and the Index Tuning Wizard should be used wherever it is possible.

Query optimization cost can increase substantially if there are many indexed views that the query optimizer may consider for a particular query. A query optimizer may consider all indexed views that are defined on any subset of tables in the query. Each view has to be parsed and then investigated for the potential substitution before it is rejected. This may take same time, especially if there are hundreds of such views for a given query.

A view must meet several requirements before you can create a unique clustered index on it. During the design phase, consider these requirements:

• The view, and all tables referenced in the view, must be in the same database and have the same owner.

• The indexed view does not need to contain all the tables referenced in the query to be used by the optimizer.

• A unique clustered index must be created before any other indexes can be created on the view.

• Certain SET options (discussed later in this document) must be set correctly when the base tables, view, and index are created, and whenever data in the base tables and view are modified. In addition, the query optimizer will not consider the indexed view unless these SET options are correct.

• The view must be created using schema binding and any user-defined functions referenced in the view must be created with the SCHEMABINDING option.

• Additional disk space will be required to hold the data defined by the indexed view.

Design Guidelines

Consider these guidelines when designing indexed views:

• Design indexed views that can be used by several queries or multiple operations.

  For example, an indexed view that contains the SUM of a column and the COUNT_BIG of a column can be used by queries that contain the functions SUM, COUNT, COUNT_BIG, or AVG. The queries will be faster because only a small number of rows from the view need to be retrieved rather than the full number of rows from the base tables and a portion of the computations required for performing the AVG function have already been done.

• Keep the index compact.

  By using the fewest number of columns and bytes as possible, the optimizer gains maximum efficiency in locating the row data. Conversely, if a large clustered index key is defined, any secondary, nonclustered indexes defined on the view will be significantly larger because the nonclustered index entries will contain the clustering key in addition to the columns defined by the index.

• Consider the size of the resulting indexed view.

  In the case of pure aggregation, the indexed view may not provide any significant performance gains if its size is similar to the size of the original table.

• Design multiple smaller indexed views that accelerate parts of the process.

  You may not be able to always design an indexed view that addresses the entire query. Should that occur, consider creating several indexed views each performing a portion of the query.

  Consider these examples:

  • A frequently executed query aggregates data in one database, aggregates data in another database, and then joins the results. Because an indexed view cannot reference tables from more than one database, you cannot design a single view to perform the entire process. However, you can create an indexed view in each database that does the aggregation for that database. If the optimizer can match the indexed views against existing queries, at least the aggregation processing will be faster, without the need to recode existing queries. Although the join processing is not faster, the overall query is faster because it uses the aggregations stored in the indexed views.

  • A frequently executed query aggregates data from several tables, and then uses UNION to combine the results. UNION is not allowed in an indexed view. You can design views to perform each of the individual aggregation operations. The optimizer can then select the indexed views to speed up queries with no need to recode the queries. While the UNION processing is not improved, the individual aggregation processes are improved.

Using the Index Tuning Wizard

The Index Tuning Wizard recommends indexed views in addition to recommending indexes on base tables. Using the wizard enhances an administrator's ability to determine the combination of indexes and indexed views that optimize the performance of the typical mix of queries executed against a database.

Because the Index Tuning Wizard forces all the required SET options (to ensure the result set is correct), its indexed view creation will succeed. However, your application may not be able to take advantage of the views if its option settings are not set as required. The inserts, updates, or deletes may fail on tables that participate in the indexed view definitions.

Maintaining Indexed Views

SQL Server automatically maintains indexed views similar to any other index. In the case of ordinary indexes, each index is tied directly to a single table. With each INSERT, UPDATE, or DELETE operation performed on the underlying table, the index is updated accordingly so that the values stored in the index are always consistent with the table.

Indexed views are similarly maintained. However, if the view references several tables, updating any of them may require updating the indexed view. Unlike ordinary indexes, a single row insert into any of the participating tables may cause multiple row inserts into the indexed view. The same is true for updates and deletes. Consequently, the maintenance of an indexed view may be more expensive than maintaining an index on the table.

In SQL Server 2000, some views can be updated. When a view is updatable, the underlying base tables are modified directly through the view using INSERT, UPDATE and DELETE statements. Creating an index on a view does not prevent the view from being updatable. For more information about updatable views, see Modifying Data Through a View in SQL Server Books Online for SQL Server 2000.

Maintenance Cost Considerations

The following points should be considered when designing indexed views:

• Additional storage is required in the database for the indexed view. The result set of an indexed view is physically persisted in the database in a manner similar to that of typical table storage.

• SQL Server maintains views automatically. Therefore, any changes to a base table on which a view is defined may initiate one or more changes in the view indexes. Thus, additional maintenance overhead is incurred.

The net performance improvement achieved by a view is the difference of the total query execution savings offered by the view and the cost to store and maintain the view.

It is relatively easy to approximate the required storage the view will consume. Evaluate the SELECT statement encapsulated by the view definition with the SQL Query Analyzer tool Display Estimated Execution Plan. This tool will yield an approximation of the number of rows returned by the query and the size of the row. By multiplying these two values together, it is possible to approximate the potential size of the view. However, this is only an approximation. The actual size of the index on the view can be accurately determined only by creating the index on the view.

From the standpoint of automated maintenance considerations performed by SQL Server, the Display Estimated Execution Plan functionality may give some insight on the impact of this overhead. If a statement that modifies the view (UPDATE on the view, INSERT into a base table) is evaluated with SQL Query Analyzer, the SHOWPLAN will include the maintenance operation for that statement. Taking this cost into consideration along with an idea of how many times this operation will occur in the production environment may indicate the potential cost of view maintenance.

As a general recommendation, any modifications or updates to the view or the base tables should be performed in batches rather than singleton operations whenever possible. This may reduce some overhead in the view maintenance.

Creating Indexed Views

The steps required to create an indexed view are critical to the successful implementation of the view.

1. Verify the SET options are correct for all existing tables that will be referenced in the view.

2. Verify your session's SET options are set correctly before creating any new tables and the view.

3. Verify the view definition is deterministic.

4. Create the view using the WITH SCHEMABINDING option.

5. Create the unique clustered index on the view.

Using SET Options to Obtain Consistent Results

Evaluating the same expression can produce different results in SQL Server if different SET options are active when the query is executed. For example, after the SET option CONCAT_NULL_YIELDS_NULL is set to ON, the expression 'abc' + NULL returns the value NULL. However, after CONCAT_NULL_YIEDS_NULL is set to OFF, the same expression produces 'abc'. Indexed views require fixed values for several SET options to ensure that the views can be maintained correctly and return consistent results.

The SET options in the following table must be set to the values shown in the Required Value column whenever these conditions occur:

• The indexed view is created.

• There is any INSERT, UPDATE, or DELETE operation performed on any table participating in the indexed view.

• The indexed view is used by the query optimizer to produce the query plan.

SET Options	RequiredValue	DefaultServerValue	OLE Db and ODBC Value	DB LIB Value
ANSI_NULLS	ON	OFF	ON	OFF
ANSI_PADDING	ON	ON	ON	OFF
ANSI_WARNING	ON	OFF	ON	OFF
ARITHABORT	ON	OFF	OFF	OFF
CONCAT_NULL_YIELDS_NULL	ON	OFF	ON	OFF
NUMERIC_ROUNDABORT	OFF	OFF	OFF	OFF
QUOTED_IDENTIFIER	ON	OFF	ON	OFF

If you are using an OLE DB or ODBC server connection, the only value that must be modified is the ARITHABORT setting. All DB LIB values must be set correctly either at the server level using sp_configure or from the application using the SET command. For more information about SET options, see Using Options in SQL Server in SQL Server Books Online for SQL Server 2000.

Using Deterministic Functions

The definition of an indexed view must be deterministic. A view is deterministic if all expressions in the select list, as well as the WHERE and GROUP BY clauses, are deterministic. Deterministic expressions always return the same result any time they are evaluated with a specific set of input values. Only deterministic functions may participate in deterministic expressions. For example, the DATEADD function is deterministic because it always returns the same result for any given set of argument values for its three parameters. GETDATE is not deterministic because it is always invoked with the same argument, yet the value it returns changes each time it is executed. For more information, see Deterministic and Nondeterministic Functions in SQL Server Books Online for SQL Server 2000.

Even if an expression is deterministic, if it contains float expressions, the exact result may depend on the processor architecture or version of microcode. To ensure data integrity in SQL Server 2000, such expressions can participate only as non-key columns of indexed views. Deterministic expressions that do not contain float expressions are called precise. Only precise deterministic expressions may participate in key columns and WHERE or GROUP BY clauses of indexed views.

Use the COLUMNPROPERTY function and IsDeterministic property to determine if a view column is deterministic. Use the COLUMNPROPERTY function and IsPrecise property to determine if a deterministic column in a view with schemabinding is precise. COLUMNPROPERTY returns 1 if TRUE, 0 if FALSE, and NULL for invalid input (the column is not deterministic). For example, SELECT COLUMNPROPERTY(Object_Id('Vdiscount1'),'SumDiscountPrice','IsPrecise') returns 0 because the SumDiscountPrice column references float column Discount from the table Order Details. Alternatively, the column SumPrice in the same view is both deterministic and precise.

Note: The view that this SELECT statement is based on can be found in the example section as View 1.

Additional Requirements

In addition to the requirements listed in the design guidelines, the Using Set Options to Obtain Consistent Results and Using Deterministic Functions sections, the following requirements must be met.

Base table requirements

• Base tables must have the correct SET options set at the time the table is created or it cannot be referenced by the view with schemabinding.

• Tables must be referenced by two-part names (owner.tablename) in the view definition.

Function requirements

• User-defined functions must be created using the WITH SCHEMABINDING option.

• User-defined functions must be referenced by two-part names (owner.function).

View requirements

• The view must be created using the WITH SCHEMABINDING option.

• The view must reference only base tables in the same database, not other views.

Syntax restrictions

There are several restrictions on the syntax of the view definition. The view definition must not contain the following:

• COUNT(*)

• ROWSET function

• Derived table

• self-join

• DISTINCT

• STDEV, VARIANCE, AVG

• Float*, text, ntext, image columns

• Subquery

• full-text predicates (CONTAIN, FREETEXT)

• SUM on nullable expression

• MIN, MAX

• TOP

• OUTER join

• UNION

Note: The indexed view may contain float columns, however, such columns cannot be included in the clustered index key.

GROUP BY restrictions

If GROUP BY is not used, expressions cannot be used in the select list.

If GROUP BY is present, the VIEW definition:

• Must contain COUNT_BIG(*).

• Must not contain HAVING, CUBE, or ROLLUP.

These restrictions are applicable only to the indexed view definition. A query can use an indexed view in its execution plan even if it does not satisfy these GROUP BY restrictions.

Index Requirements

• The user executing the CREATE INDEX statement must be the view owner.

• If the view definition contains a GROUP BY clause, the key of the unique clustered index can reference only the columns specified in the GROUP BY clause.

Examples

The examples in this section illustrate the use of indexed views with two major groups of queries: aggregations and joins. They also demonstrate the conditions used by the query optimizer when determining if an indexed view is applicable. For information about a complete list of conditions, see How the Query Optimizer Uses Indexed Views.

The queries are based on tables in Northwind, the sample database provided in SQL Server 2000, and can be executed as written. You may want to use the Show Execution Plan tool in SQL Query Analyzer to view the plans selected by the query optimizer before and after the views are created. Although the examples demonstrate how the optimizer chooses the low cost execution plan, the Northwind sample database is too small to show performance gains.

The following queries show two methods to return the five products with the largest total discount from the Order Details table.

Query 1

SELECT TOP 5 ProductID, SUM(UnitPrice*Quantity) – 
   SUM(UnitPrice*Quantity*(1.00-Discount))AS Rebate
FROM [Order Details]
GROUP BY ProductID
ORDER BY Rebate DESC

Query 2

SELECT TOP 5 ProductID, SUM(UnitPrice*Quantity*Discount)AS Rebate
FROM [Order Details]
GROUP BY ProductID
ORDER BY Rebate DESC

The execution plan selected by the query optimizer contains:

• A Clustered Index Scan on the Order Details table with a row estimate of 2,155 rows.

• A Hash Match/Aggregate operator that puts the selected rows into a hash table based on the GROUP BY column and computes the SUM aggregation for each row.

• A TOP 5 sort operator based on the ORDER BY clause.

View 1

Adding an indexed view that includes the aggregations required for the Rebate column will change the query execution plan for Query 1. On a large table (multi-million rows), the query's performance would also improve significantly.

CREATE VIEW Vdiscount1 WITH SCHEMABINDING 
AS
SELECT SUM(UnitPrice*Quantity)AS SumPrice, 
   SUM(UnitPrice*Quantity*(1.00-Discount))AS
   SumDiscountPrice, COUNT_BIG(*) AS Count, ProductID 
FROM dbo.[Order Details]
GROUP BY ProductID
   GO
CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount1 (ProductID)

The execution plan for the first query shows that the Vdiscount1 view is used by the optimizer. However, the view will not be used by the second query because it does not contain the SUM(UnitPrice*Quantity*Discount) aggregate. Another indexed view can be created that will address both queries.

View 2

CREATE VIEW Vdiscount2 WITH SCHEMABINDING
AS
SELECT SUM(UnitPrice*Quantity)AS SumPrice,
   SUM(UnitPrice*Quantity*(1.00-Discount))AS SumDiscountPrice, 
   SUM(UnitPrice*Quantity*Discount)AS SumDiscountPrice2, COUNT_BIG(*) AS
 Count, ProductID
FROM dbo.[Order Details]
GROUP BY ProductID
GO
CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount2 (ProductID)

With this indexed view, the query execution plan for both queries now contains:

• A Clustered Index Scan on the Vdiscount2 view with a row estimate of 77 rows

• A TOP 5 Sort function based on the ORDER BY clause

The query optimizer selected the view because it provided the lowest execution cost even though it was not referenced in the query.

Query 3

Query 3 is similar to the previous queries, but ProductID is replaced by the column OrderID, which is not included in the view definition. This violates the condition that all expressions in the query select list must be derivable from the view select list from the tables not included in the view definition.

SELECT TOP 3 OrderID, SUM(UnitPrice*Quantity*Discount) OrderRebate
FROM dbo.[Order Details]
GROUP BY OrderID
ORDER BY OrderRebate desc

A separate indexed view would be required to address this query. Vdiscount2 could be modified to include OrderID, however, the resulting view would contain as many rows as the original table and would not provide a performance improvement over using the base table.

Query 4

This query produces the average price for each product.

SELECT ProductName, od.ProductID, AVG(od.UnitPrice*(1.00-Discount)) 
AS AvgPrice, SUM(od.Quantity) AS Units 
FROM [Order Details] od, Products p 
WHERE od.ProductID=p.ProductID 
GROUP BY ProductName, od.ProductID

Complex aggregates (for example, STDEV, VARIANCE, AVG) cannot be included in the definition of an index view. However, indexed views can be used to execute a query containing an AVG by including the simple aggregate functions that, when combined, perform the complex aggregation.

View 3

This indexed view contains the simple aggregate functions needed to perform an AVG function. When Query 4 is executed after the creation of View 3, the execution plan shows the view being used. The optimizer can derive the AVG expression from the view's simple aggregation columns Price and Count.

CREATE VIEW View3 WITH SCHEMABINDING 
AS 
SELECT ProductID, SUM(UnitPrice*(1.00-Discount))
   AS Price, COUNT_BIG(*) AS Count,
SUM(Quantity)AS Units 
FROM dbo.[Order Details] 
GROUP BY ProductID 
Go
CREATE UNIQUE CLUSTERED INDEX iv3 ON View3 (ProductID)

Query 5

This query is the same as Query 4, but includes one additional search condition. View 3 will work for this query even though the additional search condition references only columns from a table not included in the view definition.

SELECT ProductName, od.ProductID, AVG(od.UnitPrice*(1.00-Discount))
AS AvgPrice, SUM(od.Quantity)AS Units 
FROM [Order Details] AS od, Products AS p 
WHERE od.ProductID=p.ProductID 
AND p.ProductName like '%Tofu%' 
GROUP BY ProductName, od.ProductID

Query 6

The query optimizer cannot use View 3 for this query. The added search condition od.UnitPrice>10 contains a column from the table in the view definition but the column does not appear in the GROUP BY list nor does the search predicate appear in the view definition.

SELECT ProductName, od.ProductID, AVG(od.UnitPrice*(1.00-Discount)) 
AS AvgPrice, SUM(od.Quantity) AS Units 
FROM [Order Details] od, Products p 
WHERE od.ProductID = p.ProductID 
AND od.UnitPrice > 10 
GROUP BY ProductName, od.ProductID

Query 7

In contrast, the query optimizer can use View 3 for Query 7 because the column defined in the new search condition od.ProductID in (1,2,13,41) is included in the GROUP BY clause in the view definition.

SELECT ProductName, od.ProductID, AVG(od.UnitPrice*(1.00-Discount)) 
   AS AvgPrice, SUM(od.Quantity) AS Units
FROM [Order Details] AS od, Products AS p
WHERE od.ProductID = p.ProductID
AND od.ProductID in (1,2,13,41)
GROUP BY ProductName, od.ProductID

View 4

This view will satisfy the conditions for Query 6 by including the column od.Discount in the view definition.

CREATE VIEW View4 WITH SCHEMABINDING 
AS 
SELECT ProductName, od.ProductID, SUM(od.UnitPrice*(1.00-Discount)) 
   AS AvgPrice, SUM(od.Quantity) AS Units, 
   COUNT_BIG(*) AS Count 
FROM dbo.[Order Details] AS od, dbo.Products AS p 
WHERE od.ProductID = p.ProductID 
AND od.UnitPrice > 10
GROUP BY ProductName, od.ProductID
GO
CREATE UNIQUE CLUSTERED INDEX VdiscountInd 
   ON View4 (ProductName, ProductID)

Query 8

The same index on View 4 will also be used for a query where a join to the table Orders is added. This query meets the condition that the tables listed in the query FROM clause are a superset of the tables in the FROM clause of the indexed view.

SELECT ProductName, od.ProductID, AVG(od.UnitPrice*(1.00-Discount)) 
   AS AvgPrice, SUM(od.Quantity) AS Units 
FROM dbo.[Order Details] AS od, dbo.Products AS p, dbo.Orders AS o 
WHERE od.ProductID = p.ProductID and o.OrderID = od.OrderID 
AND od.UnitPrice > 10 
GROUP BY ProductName, od.ProductID

The final two queries are modifications of Query 8. Each modification violates one of the optimizer conditions and unlike Query 8, cannot use View 4.

Query 8a

Q8a cannot use the indexed view because of the WHERE clause mismatch between UnitPrice > 10 in the view definition and UnitPrice > 25 in the query. The query search condition predicate must be a superset of the search condition predicates in the view definition.

SELECT ProductName, od.ProductID, AVG(od.UnitPrice*(1.00-Discount)) 
   AvgPrice, SUM(od.Quantity) AS Units 
FROM dbo.[Order Details] AS od, dbo.Products AS p, dbo.Orders AS o 
WHERE od.ProductID = p.ProductID and o.OrderID = od.OrderID 
AND od.UnitPrice > 25 
GROUP BY ProductName, od.ProductID

Query 8b

Observe that table Orders does not participate in the indexed view V4 definition. In spite of that, adding a predicate on this table will disallow using the indexed view because the added predicate may eliminate additional rows participating in the aggregates as it is shown in Query 8b.

SELECT ProductName, od.ProductID, AVG(od.UnitPrice*(1.00-Discount)) 
   AS AvgPrice, SUM(od.Quantity) AS Units 
FROM dbo.[Order Details] AS od, dbo.Products AS p, dbo.Orders AS o 
WHERE od.ProductID = p.ProductID and o.OrderID = od.OrderID 
AND od.UnitPrice > 10 
AND o.OrderDate > '01/01/1998' 
GROUP BY ProductName, od.ProductID

For More Information

Microsoft SQL Server 2000 Books Online contains more information about indexed views. For additional information, see these resources:

• Microsoft SQL Server Web site at https://www.microsoft.com/sql/.

• Microsoft SQL Server Developer Center at https://msdn.microsoft.com/library/default.asp?URL=/sqlserver/.

• SQL Server Magazine at https://www.sqlmag.com/.

• Microsoft.public.sqlserver.server and microsoft.public.sqlserver.datawarehouse newsgroups at news://news.microsoft.com.

• Microsoft Official Curriculum courses on SQL Server. For up-to-date course information, see the Microsoft Training and Services Web site at https://www.microsoft.com/learning/