Views - Pro SQL Server Internals - page 187

In an OLTP system, you need to consider carefully the pros and cons of indexed views on a case-by-case basis.
It is better to avoid indexed views if the underlying data is volatile. The view we created above is an example of what
should not be done in systems where data in the OrderLineItems table is constantly changing.
Another case where indexed views can be beneficial is join optimization. One system I dealt with had a
hierarchical security model with five levels in the hierarchy. There were five different tables, and each of them stored
information about specific permissions for every level in the hierarchy. Almost every request in the system checked
permissions by joining the data from those tables. We optimized that part of the system by creating an indexed view
that performed a five-table join so that every request performed just a single index seek operation against the indexed
view. Even though it were OLTP system, the data in the underlying tables was relatively static, and the benefits we
achieved exceeded the overhead of the indexed view maintenance.
While indexed views can be created in every edition of SQL Server, their behavior is indeed edition-specific.
Non-Enterprise editions of SQL Server need to reference a view directly in the queries using WITH (NOEXPAND) hint in
order to use the data from the indexed view. Without the hint, SQL Server expands the indexed view definition and
replaces it with an underlying query similar to the regular views. Enterprise and Developer editions do not require
such hints. SQL Server can utilize the indexed views even when you do not reference them in the query.
Now let's return to our previous example. In Enterprise edition, when you run the original query shown in
Listing 9-19, you would still get the execution plan that utilizes it, as shown in Figure 9-9 :
Listing 9-19. 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
Figure 9-9. Execution plan of the query that does not reference the indexed view (Enterprise or Developer editions)
In fact, the Enterprise edition of SQL Server can use indexed views for any queries, regardless of how close they
are to the view definition. For example, let's run the query that selects the list of all of the products ever sold in the
system. The query is shown in Listing 9-20.
Listing 9-20. Indexed views: Query that returns the list of all of the products ever sold in the system
select p.ProductId, p.Name
from dbo.Products p
where
exists
(
select *
from dbo.OrderLineItems o
where p.ProductId = o.ProductId
)