Database Reference
In-Depth Information
FROM Purchasing.PurchaseOrderDetail AS pod
GROUP BY pod.ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX iv
ON Purchasing.IndexedView (ProductID);
GO
Certain constructs such as
AVG
are disallowed. (For the complete list of disallowed constructs, refer to SQL Server
Books Online.) If aggregates are included in the view, like in this one, you must include
COUNT_BIG
by default.
The indexed view materializes the output of the aggregate functions on the disk. This eliminates the need
for computing the aggregate functions during the execution of a query interested in the aggregate outputs.
For example, the third query requests the sum of
ReceivedQty
and
RejectedQty
for certain products from the
PurchaseOrderDetail
table. Because these values are materialized in the indexed view for every product in the
PurchaseOrderDetail
table, you can fetch these preaggregated values using the following
SELECT
statement on the
indexed view:
SELECT iv.ProductID,
iv.ReceivedQty,
iv.RejectedQty
FROM Purchasing.IndexedView AS iv;
As shown in the execution plan in Figure
9-11
, the
SELECT
statement retrieves the values directly from the
indexed view without accessing the base table (
PurchaseOrderDetail
).
Figure 9-11.
Execution plan with an indexed view
The indexed view benefits not only the queries based on the view directly but also other queries that
may be interested in the materialized data. For example, with the indexed view in place, the three queries on
PurchaseOrderDetail
benefit without being rewritten (see the execution plan in Figure
9-12
for the execution plan
from the first query), and the number of logical reads decreases, as shown here:
Table 'Product'. Scan count 1, logical reads 13
Table 'IndexedView'. Scan count 1, logical reads 4
CPU time = 0 ms, elapsed time = 88 ms.
Table 'Product'. Scan count 1, logical reads 13
Table 'IndexedView'. Scan count 1, logical reads 4
CPU time = 0 ms, elapsed time = 0 ms.
Table 'IndexedView'. Scan count 0, logical reads 10
Table 'Product'. Scan count 1, logical reads 2
CPU time = 0 ms, elapsed time = 41 ms.
