Hi Geeks,

Today I will be talking about SQL Server expand views hint and its impact on execution plan. We will create an indexed view first then dive into the topic.

Now, let us run following SQL statement and see the execution plan.

vw_CIScan_QueryPlan

Above plan uses indexed view instead of query. Now let us use EXPAND VIEWS hint then find out if execution plan looks something different from the above.

vw_ExpandView_QueryPlan

What makes it different? Answer is, SQL Server engine expand views in early steps of the query optimization process to be specific during binding. When a view definition is expanded, it include tables used in the view and SQL engine will match any query to the indexed view and in our example VW_PurcOrdDtCnt. The EXPAND VIEWS query hint removes this step. So, it won’t consider any indexes defined on views for similar SQL statements and hence the plan is different with the hint.

We can safely drop the view now.

You can find index to the execution plan series here and click on One operator a day to visit exclusive page for this series.

Happy Learning!

Regards,

Kanchan

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook