Export (0) Print
Expand All

Chapter 36 - Using Views with a View on Performance

Microsoft® SQL Server™ 2000 supports the ability to create virtual tables known as views. Views provide a useful mechanism for restricting users to certain subsets of data and allowing users to access customized logical aspects of the data. Views usually contain multiple base-table joins and complex aggregations or return large result sets; therefore, without the aid of an index, views frequently suffer from poor performance.

In SQL Server 2000, the functionality of SQL Server views is expanded to provide system performance benefits through the use of indexed views. Creating a unique clustered index on a view, as well as nonclustered indexes, can improve data-access performance on the most complex queries.

Note Indexed views can be created in any edition of SQL Server 2000. In SQL Server 2000 Enterprise Edition, the indexed view will be automatically considered by the query optimizer. To use an indexed view in all other editions, the NOEXPAND hint must be used.

What Is an Indexed View?

Cc917663.spacer(en-us,TechNet.10).gif Cc917663.spacer(en-us,TechNet.10).gif

An indexed view is any view that has a unique clustered index. At the time a CREATE INDEX statement is executed on a view, the result set for the view is materialized (expanded) and stored in the database with the same structure as a table that has a clustered index. The indexed view automatically reflects modifications made to the data in the base tables after the index is created, the same way an index created on a base table does. The requirement that the view's clustered index be unique improves the efficiency with which SQL Server can find the rows in the index that are affected by any data modification.

When a standard view is created, the meta data (or description of the 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 materialized in place of the reference to the view. After the view is expanded, the query optimizer compiles a single execution plan for the executing query.

When you use a nonindexed view, the result set of a view is materialized at run time. Any computations, such as joins or aggregations, are performed during query execution for each query that references 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, eliminating the need for full materialization.) When you use a unique clustered index that is created on the view, the result set of the view is materialized immediately and persisted in physical storage in the database, saving the overhead of performing this costly operation at execution time.

Performance Gains from Indexed Views

Using indexed views provides 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 following illustration shows 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 multimillion-row tables from a real production environment.

Cc917663.c36001(en-us,TechNet.10).gif

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 that are not covered by the clustered index, the optimizer can choose one or more secondary indexes in the plan to 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 ongoing maintenance. Careful consideration should be given to finding the right balance of indexes and maintenance overhead.

Getting the Most from Indexed Views

Cc917663.spacer(en-us,TechNet.10).gif Cc917663.spacer(en-us,TechNet.10).gif

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. When indexed views are used, however, 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, which substantially reduces the cost of the query execution.

It is important to analyze your database workload before implementing indexed views. Not all queries will benefit from 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.

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

Applications that benefit from using indexed views include:

  • Decision support workloads 

  • Data marts 

  • Online analytical processing (OLAP) stores and sources 

  • Data mining workloads 

Benefiting applications can be characterized as those that contain these, or a combination of these, query types and patterns:

  • 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 

Note 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 indexed view can be used in a query execution in these ways:

  • The query can reference the indexed view directly in the FROM clause of the SELECT statement. 

  • 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 latter 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.

Optimizer Considerations

The query optimizer considers several conditions to determine whether 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 FROM clause of the query must be a superset of the tables in the FROM clause of the indexed view. 

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

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

  • All expressions in the query select list must be derivable from the view select list or from the tables that are 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 component 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 in the view definition. 

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

If the query contains more than one FROM clause (subqueries, derived tables, or 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 (a 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 recommended.

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, you 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 you use this option, the query optimizer ignores all view indexes when estimating the low-cost method of covering the columns referenced in the query.

Designing Indexed Views

Cc917663.spacer(en-us,TechNet.10).gif Cc917663.spacer(en-us,TechNet.10).gif

Identifying an appropriate set of indexes for a database system can be complex. Numerous possibilities should be considered 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 (for example, SUM). 

Indexes on tables and indexed views should be designed concurrently to get 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. Tuning the physical design of a database creates trade-offs between the performance requirements of a diverse set of queries and updates that the database system must support. Identifying an appropriate physical design for indexed views is a challenging task, and the Index Tuning Wizard should be used whenever it is possible.

Query optimization cost may increase substantially if the query optimizer must consider many indexed views. A query optimizer may consider all indexed views that are defined on any subset of tables in the query. It must parse and investigate each view for the potential substitution before it is rejected. This may take same time, especially if a given query has hundreds of such views.

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 (see "Using SET Options to Obtain Consistent Results" later in this chapter) must be set correctly when the base tables, view, and index are created, and whenever data in the base tables and view are modified. 

    Note 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 also be created with the SCHEMABINDING option. 

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

Guidelines for Designing Indexed Views

Consider these guidelines when you design 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 always be able to design an indexed view that addresses the entire query. Should that situation 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. Although the processing for UNION is not improved, the individual aggregation processes are improved. 

Using the Index Tuning Wizard

The Index Tuning Wizard recommends indexed views in addition to 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), the wizard successfully creates the indexed views.Your application may not be able to take advantage of the views, however, 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 relates directly to a single table. After 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.

Maintenance Cost Considerations

When you design indexed views, consider these points:

  • 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, so 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.

Approximating the required storage the view will consume is relatively easy. To evaluate the SELECT statement encapsulated by the view definition, in SQL Query Analyzer, on the Query menu, click Display Estimated Execution Plan. This 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. This is only an approximation, however. 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 command 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.

Creating Indexed Views

Cc917663.spacer(en-us,TechNet.10).gif Cc917663.spacer(en-us,TechNet.10).gif

The following steps are required to create an indexed view and 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 that the SET options for your session 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. But 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. 

  • Any insert, update, or delete operation is 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

Required value

Default server value

OLE DB and ODBC value

DB Libraryvalue

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-Library 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.

Using Deterministic Functions

The definition of an indexed view must be deterministic. A view is deterministic if all expressions referenced in the view's select list, 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. Therefore, 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.

Even if an expression is deterministic, if it contains float expressions, the exact result may depend on the processor architecture, the version of processor microcode, or SQL Server algorithms for evaluating floating-point expressions. To ensure data integrity in SQL Server 2000, such expressions can participate only as nonkey 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 whether a view column is deterministic. Use the COLUMNPROPERTY function and IsPrecise property to determine whether 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')

This query 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 "View 1: Including Aggregations in the View" in "Indexed Views Examples" later in this chapter.

Additional Requirements for Indexed Views

In addition to the design requirements for creating indexed views presented previously in this chapter, 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. 

  • The view must be indexable.

    Note Use the IsIndexable property of OBJECTPROPERTY to verify the index capability of the view. For example: 

    SELECT OBJECTPROPERTY(object_id('View Name'),'IsIndexable')

    This statement will return the value 1 if the view can be indexed, or 0 if the view cannot be indexed. This statement may consume significant computer resources because the evaluation of the IsIndexable property requires the parsing of the view definition, normalization, and partial optimization. 

Syntax Restrictions

The syntax of a view definition has several restrictions. The following table lists the items that a view definition must not contain.

COUNT(*)

ROWSET function

Derived table

Self-join

DISTINCT

STDEV, VARIANCE, or AVG

float*, text, ntext, or image columns

Subquery

Full-text predicates (CONTAIN or FREETEXT)

SUM on nullable expression

MIN or MAX

TOP

OUTER join

UNION

* 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(*). 

  • 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. 

Indexed View Examples

Cc917663.spacer(en-us,TechNet.10).gif Cc917663.spacer(en-us,TechNet.10).gif

This section provides examples to show the use of indexed views with two major groups of queries: aggregations and joins. These examples also demonstrate the conditions used by the query optimizer when determining whether an indexed view is applicable. For information about query optimizer conditions, see "How the Query Optimizer Uses Indexed Views" earlier in this chapter.

The queries are based on tables in the Northwind sample database in SQL Server 2000, and can be executed as written. To view the plans selected by the query optimizer before and after the views are created in SQL Query Analyzer, on the Query menu, click Show Execution Plan. Although the examples demonstrate how the optimizer chooses the low cost execution plan, the Northwind sample database is too small to show performance gains. These code examples are also available on the SQL Server 2000 Resource Kit CD-ROM in file, \Docs\ChapterCode\CH36Code.txt. For more information, see Chapter 39, "Tools, Samples, eBooks, and More."

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

Query 1: Using Aggregate Functions (Method 1)

Code Example 36.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: Using Aggregate Functions (Method 2)

Code Example 36.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 for both queries 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 column in the GROUP BY clause and computes the SUM aggregation for each row. 

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

View 1: Including Aggregations in the View

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 (multimillion rows), the performance of the query would also improve significantly.

Code Example 36.3 

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 optimizer uses the Vdiscount1 view. However, the view will not be used by the second query because it does not contain the SUM(UnitPrice*Quantity*Discount) aggregation. Another indexed view can be created that will address both queries.

View 2: Expanding View 1 Functionality

Code Example 36.4 

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 selects the view because it provides the lowest execution cost although it was not referenced in either query.

Query 3: Including Columns from Other Tables

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

Code Example 36.5 

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

A separate indexed view is required to address this query. The view Vdiscount2 can be modified to include OrderID; however, the resulting view contains as many rows as the original table and does not provide a performance improvement over using the base table.

Query 4: Using Complex Aggregations

Query 4 produces the average price for each product by using the complex AVG aggregate function.

Code Example 36.6 

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 aggregate functions (for example, STDEV, VARIANCE, or 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: Using Simple Aggregate Functions to Create Complex Functions

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.

Code Example 36.7 

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: Adding Search Condition Columns from Other Tables

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

Code Example 36.8 

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: Adding Search Condition Columns Without a GROUP BY Clause

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.

Code Example 36.9 

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: Using a GROUP BY Clause

In contrast to Query 6, 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.

Code Example 36.10 

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: Adding Search Condition Columns to the View

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

Code Example 36.11 

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: Using Table Joins

The same index on View 4 is also used for a query where a join to the Orders table 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.

Code Example 36.12 

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: Search Condition Mismatches

This query cannot use the indexed view because of the mismatch between the WHERE clause 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.

Code Example 36.13 

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: Adding a Search Predicate on a Joined Table

In this query, the Orders table does not participate in the indexed view definition in View 4. Nevertheless, adding a predicate on this table disallows using the indexed view, because the added predicate may eliminate additional rows participating in the aggregations as it is shown in Query 8b.

Code Example 36.14 

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 

Cc917663.spacer(en-us,TechNet.10).gif

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft