Indexed Views and the NOEXPAND Hint

In most cases views are expanded at run time to be replace with the underlying definition. But if you have an indexed view, you can force SQL Server to treat the view as if it were a real table, essentially disabling expansion on that view for that query. We'll show you a normal view and then contrast that query plan with one against an indexed view, and then add the NOEXPAND hint to show you the final plan that is possible. Remember that indexed views are a powerful and valuable technique, but they do add overhead, so use only when you have too!

Andy Warren is a software trainer focusing on SQL Server, a member of the PASS Board of Directors, and a principal in this site - SQLShare.com.

gagan khurana on 12/5/2010

great!!

Surya Prakash on 12/6/2010

good

Prerna on 12/7/2010

Very Nice !

Kasey Wheeler on 12/8/2010

Good video.

Don Weigend on 12/8/2010

Excellent lesson, thank you!

AFA8D96B5A on 12/8/2010

Thanks... but I still did not understand WHY I need to use the (noexpand) option. You said there are no performance gains... then why should I bother?

pranab on 12/8/2010

good

Luis Cintron on 12/8/2010

Awesome

John on 12/8/2010

Useful stuff - now for more on schema binding

John on 12/8/2010

Useful stuff - now for more on schema binding

Audrey Michiels on 12/8/2010

I didn't really understand what the difference is between a indexed view with or without noexpand. The video only tells it is differant by showing the Execution Plan.

Mat on 12/8/2010

didn't understand the point

Juan Rivero on 12/8/2010

No indication as to how this is useful

Gilles Despaties on 12/10/2010

Another feather on my hat. I saw indexes on views as complex.... Not anymore.. THANKS

linda on 12/10/2010

Gosh I wish you would have added 30 seconds to mention what advantages or disadvantages there are to using NoExpand.

Simon on 12/13/2010

Good, but its speed is too slow. If it can be download, it will be better.

Satyabodhi Densmore on 12/15/2010

I am curious about support for indexed views in the standard edition. Do you get to use the indexed view in Standard Edition if you specify NOEXPAND or is this hint simply ignored?

Greg Miller on 12/20/2010

Great Job

Mohamed Irshad on 12/31/2010

very useful

Anil Babu on 2/26/2011

good

Michael on 5/3/2011

Good

Maurice Ivory on 6/29/2011

Good stuff, I leaned about a new funcntion I didn't know about.

MohanD on 12/10/2011

Would have loved to see the stats on the performance gain

Michael on 2/8/2012

Good

om on 3/16/2012

It is great concept, I did not find like this while I google it.