The NOEXPAND and EXPAND VIEWS hints

Before talking about the NOEXPAND and EXPAND VIEWS hints, let’s discuss 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 3, The Query Optimizer, 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 Enterprise Edition, SQL Server may match the query to an existing indexed view. So, 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. Therefore, this hint only has an effect in SQL Server Enterprise Edition.