SQL SERVER – Using NOEXPAND with Indexed View

As you might have seen this week, I have been sharing simple questions which I have received from my clients of Comprehensive Database Performance Health Check. I was helping one of my clients with tuning a view (my personal opinion, I do not like views), when I used NOEXPAND with the Indexed view, the performance of the query on the view was very much optimized. The client asked what actually NOEXPAND do? Let us quickly discuss that today.

Let me be very clear that I personally do not like to use views in my SQL Server development. I have always found that using stored procedures, table-valued functions, and other solutions are much better than views and indexed views.

Views

When you use any view, there is a good chance that SQL Server will use the underlying tables and objects in the view to build the final execution plan. Lots of people incorrectly think that when they create a view and view is used in their SELECT statement, it will use the data from the view. If you just have a view, it might not contain any data at all. All the data is retrieved run time from the view.

Indexed View

However, when you create an indexed view (which is also known as materialized view in other relational databases), it actually contains the data. However, there is a good chance that SQL Server may still use underlying data and indexes to retrieve your results instead of using the view. If you want to force your queries to use data contained in the Indexed view, it is suggested that you use NOEXPAND hint and that will force SQL Server to use the data containing in the views.

Real-world Stories

In reality, I was able to tune my client’s query with the hint NOEXPAND but there is a good chance that the same hint will not do good and give poor performance. There have been cases where I was able to gain speed of query by just removing the NOEXPAND hint, so I suggest that you try out your queries with both methods.

I have written a few good blog posts around views and I suggest that you read them:

If you liked this blog, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.

Here are my few recent videos and I would like to know what is your feedback about them.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Menu
Exit mobile version