Hints - Inside the SQL Server Query Optimizer - page 229

Figure 7-16: Plan using a FAST N hint.
There is also a FASTFIRSTROW hint, but it is not as flexible as FAST N , as you can
specify any number for N. Essentially, FASTFIRSTROW would be the same as specifying
the FAST 1 hint.
NOEXPAND, EXPAND VIEWS hints
Before talking about the NOEXPAND and EXPAND VIEWS hints, let me explain the default
behavior of queries when using indexed views so that you can see how these hints can
change this behavior.
As explained in Chapter 5, The Optimization Process , SQL Server expands views in the
early steps of query optimization during binding, when a view reference is expanded
to include the view definition; for example, to directly include the tables used in
the view. This behavior is the same for every edition of SQL Server. Later on in the
optimization process, but only in the Enterprise edition, SQL Server may match the
query to an existing indexed view. So, basically, the view was expanded at the beginning
but was later matched to an existing indexed view. The EXPAND VIEWS hint removes the
matching step, thus making sure the views are expanded but not matched at the end of
the optimization process. So this hint only has an effect in SQL Server Enterprise edition.
On the other hand, the NOEXPAND hint asks SQL Server not to expand any views at all,
and to try to use any existing indexed view instead. This hint works in every SQL Server