Views - Pro SQL Server Internals - page 185

Listing 9-16. Indexed views: Dashboard query

select top 10 p.ProductId, p.name as ProductName, sum(o.Quantity) as TotalQuantity
from dbo.OrderLineItems o join dbo.Products p on
o.ProductId = p.ProductId
group by
p.ProductId, p.Name
order by
TotalQuantity desc

If you run dashboard query in the system, you would receive the execution plan shown in Figure 9-6 .

Figure 9-6. Execution plan of the query that selects the top-10 most popular products

As you see, this plan scans and aggregates the data from the OrderLineItems table, which is expensive in terms of
IO and CPU. Alternatively, you can create an indexed view that does the same aggregation and materializes the results
in the database. The code to create this view is shown in Listing 9-17.

Listing 9-17. Indexed views: Indexed view creation
create view dbo.vProductSaleStats(ProductId, ProductName, TotalQuantity, Cnt)
with schemabinding
as
select p.ProductId, p.Name, sum(o.Quantity), count_big(*)
from dbo.OrderLineItems o join dbo.Products p on
o.ProductId = p.ProductId
group by
p.ProductId, p.Name
go

create unique clustered index IDX_vProductSaleStats_ProductId
on dbo.vProductSaleStats(ProductId);

create nonclustered index IDX_vClientOrderTotal_TotalQuantity
on dbo.vProductSaleStats(TotalQuantity desc)
include(ProductName);

The code in Listing 9-17 creates a unique clustered index on the ProductId column as well as a non-clustered
index on the TotalQuantity column.

■ an indexed view must have count_big(*) aggregation if group by is present. this helps to improve the
performance of the indexed view maintenance if data in the underlying tables is modified.

Note