A Quick Look at View Expansion

Views are great tools for security and abstraction, and because they look like tables we tend to think of them as tables. But what really happens when you write a query against a view? SQL Server "expands" the view before the optimization begins, essentially putting the view definition right into your query. Watch the video for a quick example!

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.

Orlando on 11/30/2010

Nice start, but too much hand-waving around optimization. Maybe a deeper dive into how the optimizer tries to rewrite the query to incorporate any tables or predicates the view uses into the outer query.

Alok Joshi on 11/30/2010

Missed to understand if NOEXPAND should be used or not?

Darryll Petrancuri on 11/30/2010

A good start, but not enough detail on the performance caveats on views, and doesn't explore potentially better alternatives live table valued functions.

Joe on 11/30/2010

Very Useful, Andy. Thanks Again.

Rubens on 11/30/2010

Good info, as always... learn something new every day! :)

Nilesh on 11/30/2010

good...why do u have time limit ? extend it for atleast 5 mins !!!

John on 11/30/2010

Learned something new again :thumbsup: to Andy

Jeff Benedict on 11/30/2010

Great, I forgot about that.

Raghavendra on 11/30/2010

you have not specified or displayed sample to use the NOEXPAND.

Satyabodhi Densmore on 11/30/2010

Need more instruction about NOEXPAND.

Jeff Stanlick on 12/1/2010

Great intro on the query plans for views.

Kenneth Wymore on 12/1/2010

good to know!

gagan khurana on 12/5/2010

great!

Mat on 12/8/2010

it was useful

Maurice Ivory on 6/17/2011

I found this very usefull, especially since I'm now working with a querying a view on a application that has been built by our team.

MohanD on 12/10/2011

I would have liked to see how much it benifits with No Expand command on querrying a view.