Improve performance with SQL Server 2000 indexed views

server| View | index | performance

What is an indexed view?

For many years, Microsoft®sql Server™ has been providing the ability to create virtual tables, called views. In the past, these views have two main uses:

Provides a security mechanism for restricting users to a subset of data in one or more base tables.
Provides a mechanism that allows developers to customize how users can view data stored in a base table in a logical way.
SQL Server 2000 has expanded the functionality of the SQL Server view to improve system performance. It can create unique clustered and non-clustered indexes on one view, improving data access performance for the most complex queries. In SQL Server 2000, a view with a unique cluster index is called an indexed view.

Note: Indexed views are just one feature of the SQL Server 2000 Enterprise Edition and the SQL Server 2000 Developer Edition.

From the standpoint of the database management system (DBMS), the view is a description of the data (metadata). When you create a typical view, you define the metadata through a SELECT statement that defines a result set that appears as a virtual table. When a view is referenced in the FROM clause of another query, the metadata is retrieved from the system directory and extended to replace the reference to the view. After the view is expanded, the query optimizer compiles a single execution plan for the query that is being executed.

If it is a non indexed view, the view is materialized at run time. Any calculation, such as a join or aggregation, is performed during the query execution for each query referencing the view. (views do not always need to be fully materialized.) A query can contain other verbs, joins, or aggregations to apply to the tables and views referenced by the view. After a unique clustered index has been created on the view, the resulting assembly of the view is immediately materialized and persisted in the physical storage space of the database in order to save the large amount of resources that this operation occupies.

When you execute a query, there are two ways to use an indexed view. Queries can refer directly to an indexed view, and more importantly, you can select an indexed view if the query optimizer determines that the view can be replaced with some or all of the query, and this is a low-cost query plan. The second scenario uses an indexed view instead of the underlying table and its normal index. At this point, you do not need to refer to the view in the query, and the query optimizer can use that view during the execution of the query. This allows existing applications to benefit from the newly created indexed view without change.

Improved performance through indexed views

It is not a new idea to use indexes to improve query performance, but indexed views also have other performance benefits that are not available with standard indexes. Indexed views can improve query performance in the following ways:

The ability to compute aggregations in advance and store them in an index to minimize costly computations during query execution.
The ability to pre-join tables and store the generated data sets.
The ability to store a combination of joins or aggregations.
The following figure illustrates how much performance the query optimizer can generally improve when using indexed views. The query provides varying degrees of complexity (for example, the number of aggregations calculated, the number of tables used, or the number of predicates), and includes a large table of millions of rows from the actual production environment.

Figure 1. How much performance can generally be improved when the query optimizer uses indexed views

Using secondary indexes for views

Additional query performance can be improved by the view's accessible non-clustered indexes. Like the secondary index of a table, the secondary index of a view can also provide more options for the query optimizer to choose from during compilation. For example, if a query includes columns that are not covered by a clustered index, the optimizer can select one or more secondary indexes in the schedule to avoid time-consuming global scans of indexed views or base tables.

Because indexes need to be maintained continuously, adding an index to a schema increases the additional cost of the database. Therefore, it should be carefully considered to find a balance between indexing and maintaining additional overhead.

Benefits of using indexed views

Before you implement an indexed view, you should analyze the workload of your database. Use your own knowledge of queries and various tools (such as SQL Analyzer) to identify queries that can benefit from indexed views. If you are aggregating and joining frequently, it is best to use an indexed view.

Not all queries will benefit from indexed views. Like a normal index, there is no benefit if an indexed view is not used. In this case, not only does it not improve performance, it also increases the cost of disk space occupancy, maintenance, and optimization. However, if indexed views are used, they can significantly improve the performance of data access (in order of magnitude). This is because the query optimizer uses the calculated results stored in the indexed view, which greatly reduces the cost of executing the query.

The query optimizer considers the use of indexed views only when the cost of a query is relatively large. This avoids trying to use a variety of indexed views when the query optimization cost exceeds the cost savings due to the use of indexed views. When the query cost is less than 1 o'clock, the indexed view is rarely used.

Applications that use indexed views to benefit include:

Determine support workload
Data mart
Online analytical Processing (OLAP) libraries and sources
Data mining effort
From the point of view of the type and pattern of the query, the benefit application can be summed up as an application containing the following content:

Joins and aggregations of large tables
Duplicate mode of query
Duplicate aggregation of identical or overlapping column sets
Repeat joins the same table for the same key word
The above combination
Conversely, indexed views are not available because there are many written online transaction processing (OLTP) systems or frequently updated databases that can increase maintenance costs by simultaneously updating both the view and the underlying base table.

How the query optimizer uses indexed views

The SQL Server query optimizer can automatically determine when an indexed view can be used in a given query execution. The optimizer can use this view to query the execution plan without directly referencing the view in the query. Therefore, you do not need to make any changes to the existing application itself, and these applications can take advantage of indexed views. The only thing to do is to create an indexed view.

Considerations for the Optimizer

The query optimizer considers several criteria to determine whether an indexed view can cover a partial query or an entire query. These conditions conform to a single from clause in the query and contain the following:

The table in the query from clause must be a superset of the tables in the indexed view FROM clause.
The join condition in the query must be a superset of the join condition in the view.
The aggregation column in the query must be a subset of the aggregated columns in the view.
All expressions in the query selection list must originate from the View selection list or from tables that are not included in the view definition.
The query search condition predicate must be a superset of the search condition predicate in the view definition. Each entry in the view search verb must appear in the query search predicate in the same form.
All the columns in the query search condition predicate, which belong to the table in the view definition, must appear in one or more of the following:
The same predicate in the view definition.
GROUP by list.
View select list (if no GROUP by list).
If the query contains multiple from clauses (subqueries, derived tables, UNION), the optimizer can select multiple indexed views to manage queries that contain multiple from clauses.

Note: An exception is also the case where the optimizer may collapse two from clauses into one (collapse a subquery to join or collapse a derived table into a join variant). If this occurs, an indexed view substitution may cover multiple from clauses in the original query.

An example of a query demonstrating these conditions is provided at the end of this document. The best recommended approach is to have the query optimizer determine which indexes, if any, are used in the query execution plan.

Using the NOEXPAND option

The NOEXPAND option forces the query optimizer to treat a view as if it were a normal table containing 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 option

In addition, the user can explicitly exclude an indexed view 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)

If this option is used, the query optimizer ignores all view indexes when evaluating low-cost methods that involve columns referenced in the query.

Considerations for Design

Finding the appropriate set of indexes for the database system is quite complex. Although there are many possibilities to consider when designing a normal index, adding an indexed view to a schema greatly increases the complexity of the design and potential results. For example, an indexed view can be used to:

Any subset of the tables referenced in the query.
Any subset of the criteria in the query (belonging to the above subset of the table)
Grouping columns.
Aggregate functions, such as SUM.
You should design both the index and indexed views of the table to get the best results from each structure. Because both indexes and indexed views can be useful for a given query, designing them individually can lead to redundant recommendations, resulting in higher storage and maintenance overhead. When adjusting the physical design of the database, it is necessary to consider the performance requirements of various query sets and the update operations that the database system must support. Therefore, finding a reasonable physical design for an indexed view is a challenging task and should be used as much as possible with the Index Tuning Wizard.

If there are many indexed views available for the query optimizer to consider for a particular query, the query optimization cost will increase significantly. The query optimizer might consider all indexed views defined for any subset of the tables in the query. Before you reject each view, you must parse it and then study whether it might be a potential replacement. This may take some time, especially if there are hundreds of views of this class for a given query.

The view must meet several requirements before you can create a unique clustered index for it. In the design phase, consider the following requirements:

All tables referenced in the view and in the view must be in the same database and have the same owner.
Indexed views do not need to contain all the tables referenced in the query that you want to use for the optimizer.
Before you can create additional indexes, you must create a unique clustered index for the view.
When you create base tables, views, and indexes, and modify data in base tables and views, you must set certain set options correctly (discussed later in this document). Also, if these SET options are correct, the query optimizer will not consider indexed views.
Views must be created using schema bindings, and any user-defined functions referenced in the view must be created using the schemabinding option.
In addition, a certain amount of disk space is required to hold the data defined by the indexed view.

Design Guidelines

When you design an indexed view, consider the following guidelines:

The indexed view of the design must be able to be used for multiple queries or multiple computations.
For example, an indexed view that contains the sum of a column and the COUNT_BIG of a column can be used for queries that contain function sum, COUNT, Count_big, or AVG. Because only a handful of rows in the view are retrieved, rather than all the rows in the base table, and part of the computation required to perform the AVG function is completed, the query is faster.

Keep the index compact.
By using a minimum number of columns and as few bytes as possible, the optimizer obtains the highest efficiency in finding row data. Conversely, if a large clustered index keyword is defined, any accessible non-clustered indexes defined for the view will increase significantly because the nonclustered index entries include the cluster keyword in addition to the columns that contain the index definition.

Consider the size of the indexed view that is generated.
In a simple aggregation scenario, using an indexed view may not significantly improve any performance if the size of the indexed view is similar to the size of the original table.

Design several smaller indexed views to speed up part of the process.
Sometimes you may not be able to design an indexed view that meets the needs of the entire query. You can consider creating some indexed views at this point, with each indexed view performing part of the query.

Consider the following example:

Frequently executed queries converge on data in one database, aggregate data from another database, and then join the results. Because indexed views cannot refer to tables in multiple databases, you cannot design a view to perform the entire process. However, you can create an indexed view for each database that you want to aggregate. If the optimizer can match an indexed view to an existing query, at least the aggregation process increases the speed because the existing query does not have to be logged. Although the join processing does not accelerate, the speed of the entire query is accelerated by using the aggregations stored in the indexed view.
A frequently executed query aggregates data from multiple tables and then uses union to combine the results. UNION is not allowed to be used in indexed views. You can design a few views to perform each individual aggregation operation. The optimizer can then select an indexed view to speed up the query without logging the query. Although the UNION process has not improved, a single aggregation process has been improved.

Using the Index Tuning Wizard

The Index Tuning Wizard also recommends that you use an indexed view in addition to the index of the base table. Use this wizard to improve the ability of an administrator to determine the combination of index and indexed views, thereby optimizing the performance of a typical mixed query performed against a database.

Because the Index Tuning Wizard enforces all the required set options (to ensure that the result set is correct), its indexed view is created successfully. However, these views may not be available if your application's options are not set according to your requirements. Insert, update, or delete operations that are performed on tables that participate in the indexed view definition may fail.

Maintaining indexed views

SQL Server automatically maintains an indexed view, which is similar to maintaining any other index. For normal indexes, each index is connected directly to a single table. By performing each INSERT, update, or DELETE operation on the underlying table, the index is updated accordingly so that the values stored in the index are always consistent with the table.

The maintenance of indexed views is similar to this. However, if the view references more than one table, updating any of those tables requires updating the indexed view. Unlike a normal index, performing a row insert on any one of the participating tables can result in multiple row inserts in the indexed view. The same is true for update and delete operations. Therefore, maintaining an indexed view is more expensive than maintaining a table's index.

In SQL Server 2000, some views can be updated. If a view can be updated, use the INSERT, UPDATE, and DELETE statements to modify the underlying base table directly from the view. Creating an index for a view does not prevent the view from being updated. For more information about updatable views, see Modify data through view in SQL Server online books, SQL Server 2000.

Considerations for maintenance costs

The following points should be considered when designing an indexed view:

An additional storage space is required in the database for indexed views. The result set of an indexed view is physically stored in a database in a way that is similar to a typical table storage space.
SQL Server automatically maintains views. Therefore, any change to the base table on which the view is defined can cause one or more changes to the view index, resulting in an increase in maintenance overhead.
The net performance improvement achieved by a view is the difference between the amount of query execution savings that the view provides and the cost of storing and maintaining the view.

It is relatively simple to estimate the amount of storage space that the view will occupy. Use the "Show Estimated execution plan" tool in SQL Query Analyzer to find the value of the SELECT statement in the view definition. The tool will draw an approximation of the number of rows and row sizes returned by the query. By multiplying the two values, you can estimate the possible size of the view. But this is only an approximation. The actual size of the view index can only be obtained precisely by creating a view index.

From the standpoint of the automated maintenance considerations that SQL Server performs, the ability to "show estimated execution plans" may have an understanding of the impact of this overhead. If you use SQL Query Analyzer to evaluate the statement that modifies the view (an UPDATE statement for the view, an INSERT statement for the base table), SHOWPLAN will include the statement's maintenance operation. Also, consider the cost and the number of times this operation will occur in the production environment, indicating the possible cost of view maintenance.

It is generally recommended that any modifications and updates made to the view or base table should be performed as many batches as possible, rather than individually. This can reduce some of the overhead of view maintenance.

Create an indexed view

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

Make sure that the SET options for all existing tables referenced in the view are correct.
Before creating any new tables and views, make sure that the SET options for the session are set correctly.
Make sure that the view definition is OK.
Use the WITH SCHEMABINDING option to create a view.
Creates a unique clustered index of the view.

Use SET options to achieve consistent results

If you enable different SET options when you execute a query, evaluation of the same expression in SQL Server can produce different results. For example, after setting set option Concat_null_yields_null to ON, the value returned by the expression ' abc ' + NULL is NULL. When the concat_null_yieds_null is set to OFF, the expression results in ' abc '. Indexed views require that the values of multiple SET options be fixed to ensure that these views are properly maintained and return consistent results.

The SET option in the following table must be set to the value shown in the required value column whenever the following conditions are present:

An indexed view was created.
Any INSERT, UPDATE, or DELETE operation was performed on any table referenced in the indexed view.
The query optimizer uses an indexed view to generate a query plan.
SET
Option requirements
The value defaults
Server
The value of OLE DB
And
ODBC value DB LIB
The value
Ansi_nulls on Off
Ansi_padding on On
Ansi_warning on Off
ARITHABORT on Off
Concat_null_yields_null on Off
Numeric_roundabort off
QUOTED_IDENTIFIER on Off

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

Using Deterministic functions

The definition of an indexed view must be deterministic. If all the expressions in the select list and the WHERE and GROUP by clauses are deterministic, the view is deterministic. As long as a deterministic expression is evaluated with a specific set of input values, the same result must be returned. Only deterministic functions can be added to deterministic expressions. For example, DATEADD is a deterministic function, because any given set of variable values is evaluated with its three parameters, always returning the same result. GETDATE is not a deterministic function because it is always called with the same variable, and the value returned after each execution is different. For more information, see "Deterministic and Nondeterministic Functions" in SQL Server online books, which is a 2000.

Even if an expression is deterministic, but if it contains a floating expression, the exact result may depend on the processor architecture or the version of the micro-code. To ensure the integrity of data in SQL Server 2000, such expressions can only be added to non-critical columns of indexed views. A deterministic expression that does not contain a floating expression is called an exact expression. Only an exact deterministic expression can be added to the key column of the indexed view and the WHERE or GROUP by clause.

Use the COLUMNPROPERTY function and the IsDeterministic property to determine whether the view column is deterministic. Use the COLUMNPROPERTY function and the Isprecise property to determine whether the deterministic columns in the view containing schema bindings are accurate. If TRUE, ColumnProperty returns 1, and if FALSE, returns 0, or NULL if the input is invalid (the column is not deterministic). For example, SELECT columnproperty (object_id (' Vdiscount1 '), ' sumdiscountprice ', ' isprecise ') returns 0 because the Sumdiscountprice column references the table Floating column Discount in the order Details. The column sumprice in the same view is both deterministic and accurate.

Note: The view on which this SELECT statement is based can be found in the Example section (view 1).

Other requirements

In addition to the requirements listed in the design guidelines, use SET options for consistent results, and use deterministic functions, you must also meet the following requirements.

Base table Requirements

The base table must set the SET option correctly when it is created, otherwise it cannot be referenced by a view that contains schema bindings.
A table must be referenced by a two-part name (owner. Table name) in the view definition.
function requirements

User-defined functions must be created using the WITH SCHEMABINDING option.
A user-defined function must be referenced by a two-part name (owner. function).
View Requirements

The view must be created with the WITH SCHEMABINDING option.
The view must refer only to base tables in the same database, not to other views.
Syntax restrictions

There are several limitations to the syntax of the view definition. The view definition cannot contain the following contents:

COUNT (*)
ROWSET function
Derived tables
Self-Join
DISTINCT
STDEV, Variance, AVG
float* columns, text columns, ntext columns, image columns
Child query
Full-text predicates (contain, FREETEXT)
SUM of nullable expressions
MIN, MAX
Top
OUTER joins
UNION
Note: Indexed views can contain floating columns, although such columns cannot be included in the cluster index keywords.

GROUP by limit

If you do not use GROUP by, an expression cannot be used in a select list.

If GROUP by is used, the VIEW definition:

Must contain COUNT_BIG (*).
shall not contain having, CUBE or ROLLUP.
These restrictions apply only to indexed view definitions. Queries can use indexed views in their execution plans, even if the indexed view does not conform to these GROUP by restrictions.

Indexing requirements

The user executing the CREATE INDEX statement must be the view owner.
If the view definition contains a GROUP BY clause, a unique cluster index keyword can refer only to the columns specified in the GROUP BY clause.

Example

The example in this section illustrates the use of indexed views in two primary queries (aggregations and joins). It also describes the criteria that the query optimizer uses when determining whether an indexed view is available. For a complete list of these conditions, see how the query optimizer uses indexed views.

Queries are based on the tables in Northwind (database samples provided in SQL Server 2000) and can be written to execute. Before and after creating a view, it is a good idea to use the Show Execution Plan tool in the SQL query optimizer to view the plan selected by the query optimizer. Although the example illustrates how the optimizer chooses the lowest-cost execution plan, the Northwind database sample is too small to reflect performance improvements.

The following query shows how to return two methods of 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:

There are an estimated 2,155 rows for the clustered index scan of the order Details table.
Hash matching/aggregation operator, which puts the selected row into a hash table based on the GROUP by column, and then calculates the SUM aggregation for each row.
The top 5 sort operator based on the ORDER BY clause.
View 1

Adding an indexed view that includes the aggregation required for the rebate column changes the query execution plan for query 1. On a large table of millions of rows, the query's performance will also be significantly improved.

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 query optimizer. However, because the view does not contain SUM (unitprice*quantity*discount) aggregations, it is not used by the second query. You can create another indexed view that satisfies both of these 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 two queries now contains:

Clustered index Scan for Vdiscount2 view with an estimated 77 rows
Top 5 Sort functions based on ORDER BY clause
The query optimizer chooses this view because it provides the lowest execution cost, although the view is not referenced in the query.

Query 3

Query 3 is similar to the previous query, except that the ProductID has been replaced by OrderID, and the column is not included in the view definition. This violates the condition that all expressions in the query selection list must be able to derive from the view selection list of the table that is 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

Requires a separate indexed view to satisfy the query. Vdiscount2 can be modified to include OrderID, but the generated view will have the same number of rows as the original table, so the performance provided will not be higher than the performance provided by using the base table.

Query 4

This query generates the average price per 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

The definition of an indexed view cannot include complex aggregations (for example, STDEV, Variance, AVG), but if the indexed view includes several simple aggregate functions that combine to perform complex aggregations, it can be used to execute queries that contain AVG.

View 3

The indexed view contains the simple aggregate functions required to perform the AVG function. When you execute Query 4 o'clock after you create view 3, the execution plan displays the view that is being used. The optimizer can derive the AVG expression from the view's simple aggregation column price and count 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

The query is the same as query 4, except that it includes an additional search condition. View 3 will also be used for this query, even if the additional search condition refers only to columns that are not included in a table 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. Additional search conditions OD. UNITPRICE>10 contains columns from a table within the view definition, and the column does not appear in the GROUP by list, and the search predicate does not come out of 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

Instead, the query optimizer can use View 3 for query 7 because of the new search condition OD. The columns defined in ProductID in (1,2,13,41) are included in the GROUP by clause within 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

The view includes column OD in the view definition. Discount, you can meet the conditions of query 6.

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 of view 4 will also be used for a query that adds a join to the table Orders. The query meets the following criteria: The table listed in the query from clause is 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 last two queries are variants of query 8. Each variant violates an optimizer condition, so unlike query 8, view 4 cannot be used.

Query 8a

Because the UnitPrice > 10 in the View definition does not match the WHERE clause between UnitPrice > 25 in the query, q8a cannot use indexed views. The query search condition predicate must be a superset of the search condition predicate 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

Note that the table Orders did not participate in the definition of the indexed view V4. However, adding a predicate in the table prohibits the use of indexed views because the added verb may eliminate other rows in the aggregation, as 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