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:
- SQL SERVER – ORDER BY Does Not Work – Limitation of the View 1
- SQL SERVER – Adding Column is Expensive by Joining Table Outside View – Limitation of the View 2
- SQL SERVER – Index Created on View not Used Often – Limitation of the View 3
- SQL SERVER – SELECT * and Adding Column Issue in View – Limitation of the View 4
- SQL SERVER – COUNT(*) Not Allowed but COUNT_BIG(*) Allowed – Limitation of the View 5
- SQL SERVER – UNION Not Allowed but OR Allowed in Index View – Limitation of the View 6
- SQL SERVER – Cross Database Queries Not Allowed in Indexed View – Limitation of the View 7
- SQL SERVER – Outer Join Not Allowed in Indexed Views – Limitation of the View 8
- SQL SERVER – SELF JOIN Not Allowed in Indexed View – Limitation of the View 9
- SQL SERVER – Keywords View Definition Must Not Contain for Indexed View – Limitation of the View 10
- SQL SERVER – View Over the View Not Possible with Index View – Limitations of the View 11
- SQL SERVER – Index Created on View not Used Often – Limitation of the View 12
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.
- Queries Using Specific Index – SQL in Sixty Seconds #180
- Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
- One Scan for 3 Count Sum – SQL in Sixty Seconds #178
- SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
- COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176
Reference:Â Pinal Dave (http://blog.SQLAuthority.com)
