Database Reference
In-Depth Information
NOEXPAND/EXPAND VIEWS Hints
NOEXPAND
and
EXPAND VIEWS
hints control how SQL Server handles indexed views. This behavior is edition-specific.
By default, Non-Enterprise Editions of SQL Server expand indexed views to their definition and do not use data from
them, even when views are referenced in the queries. You should specify a
NOEXPAND
hint to avoid this.
■
always specify a
NOEXPAND
hint when you reference an indexed view in the query if there is a possibility that the
database might be moved to the non-enterprise edition of SQL Server.
Tip
Listing 25-24 shows an example of
NOEXPAND
and
INDEX
hints, which force SQL Server to use the nonclustered
index created on the indexed view.
Listing 25-24.
NOEXPAND and INDEX hints
select CustomerID, ArticleId, TotalSales
from dbo.vArticleSalesPerCustomer
with (NOEXPAND, Index=IDX_vArticleSalesPerCustomer_CustomerID)
where CustomerID = @CustomerID
Alternatively, the
EXPAND VIEWS
hint allows SQL Server to expand an indexed view to its definition in the
Enterprise Edition. To be honest, I cannot think of use-cases when such behavior is beneficial.
■
there is a bug in SQL Server 2008-2012 that can lead to a situation where data in the indexed view is not
refreshed after the base table is updated through a
MERGE
operator. you can view the versions of the cumulative updates
Note
FAST N Hints
A
FAST N
hint tells SQL Server to generate an execution plan with the goal of quickly returning the number of rows
specified as a parameter. This can generate an execution plan with non-blocking operators, even when such a plan is
more expensive compared to one that uses blocking operators.
One possible use-case for such a hint is the application that is loading a large amount of data in the background
(perhaps caching it) and wants to display the first page of the data to the user as quickly as possible. Listing 25-25
shows an example of a query that uses such a hint.
Listing 25-25.
FAST N hint
select o.OrderId, OrderNumber, OrderData, CustomerId, CustomerName, OrderTotal
from dbo.vOrders
where OrderDate > @StartDate
order by OrderDate desc
option (FAST 50)
■
Note
you can see full list of query and table hints at:
http://technet.microsoft.com/en-us/library/ms181714.aspx
.
