May 12, 2003 at 3:31 pm
I had some questions with the way views work.
1) where does views get stored i.e. in what cache/tempdb
2) during an update of a table, how is a view referenced.
Any link or information discussing the details of view would be appreciated.
May 12, 2003 at 5:29 pm
A view is built on the fly, so it is not "stored" anywhere. It is resolved and referenced at run time.
During an update, this is just like a query against the table. It will see and respect the same locks.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 14, 2003 at 9:58 pm
Steve,
Having said that, if i have two tables with say 100,000 rows each and i have a view which joins the two tables. And later on if i query the view, will the process be as under ?
1) A view would be created with say 200,000 rows.
2) The query will be executed against the result.
Now my questions is that if my result is say only 20 rows, is it not more efficient to query the two tables directly and then join? What i understand is that the I/O involved in the earlier case would be very high as compared in the later case. Please give your valuable suggestion.
May 14, 2003 at 10:16 pm
BOL 2000 :
Using Views as Security Mechanisms
Scenarios for Using Views
Lastly read about materialized views.
May 21, 2003 at 7:17 pm
In Query Analyzer, type in a query on your view. Select the menu Query - Display Estimated Execution Plan.
You will see that the query plan shows how the UNDERLYING tables will be accessed. At run-time, the "compiled" query does not care about the view at all.
Materialised views are a different matter.
May 22, 2003 at 10:55 pm
Thanks a lot everybody. This makes me understand that for a view the execution plan is being cached in the cache. With this understanding, I was just wondering whether a view has any performance gains to a stored procedure as in both cases the execution plan would be stored. Any suggestions would be appreciated.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply