November 28, 2008 at 1:54 pm
I was wondering if there was any great difference between creating a regular view and a view with view_metadata. If I right click on a standard view and select open view, I can update individual values in the spreadsheet like window that opens up which then propagates to the underlying table.
How does CREATE VIEW View_MyTable WITH VIEW_METADATA (...) differ from CREATE VIEW View_MyTable (...)?
I guess I'm a little vague with BOL's description and was looking for some clarification. The only thing I could figure out was if there is an OLE or ODBC API layer, the view would query that layer instead of the underlying tables. If the layer changed, the view would still work with it instead of the tables. Is that basically the only purpose of this option and does anyone have an example of its real world use (skipping confidential details of course).
Thanks.
Gaby A.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
November 28, 2008 at 2:16 pm
I do not believe that it affects SSMS or the query window in any discernible way.
All VIEW_METADATA does is to change how the returned columns are defined to the Client-API's.
Normally, when a Client-API interrogates the returned dataset, the columns will be presented with the names of their "base tables", if any. That is, the table that the column's data originally came from (any column calculations will conceal this, however). Think of this as the "TABLE_METADATA".
When VIEW_METADATA is set, however, the base table name returned for the column is just the View's name. In other words, it makes the view look like it is the base table.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 28, 2008 at 2:24 pm
... And the only reasons that I can think that you would use this are:
1) Minor Security nit: because you do not want the client software to know the names of your base tables.
and
2) Client App problem: The client app does not behave correctly unless you turn this on. This could happen for instance, because you restructured a former base table into a new structure and organization in your DB and used a view of the same name make the schema appear the same to the app. But the app is trapping the base-table name of the columns, whic is no longer correct unless you turn VIEW_METADATA on.
also:
3) Possibly to conceal base tables from some reporting packages that insist on trying to copy the whole base table to the client. Make them think that the view is the base table so that they will only try to copy the view's data to the client.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 1, 2008 at 7:06 am
You would want to use it with any of the ORM tools that try to walk the structure of the data returned. By masking the table structure off behind the view, you'll get more consistent behavior out of the ORM tool. More importantly, you can adjust the query as needed without impacting the generated code.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 1, 2008 at 7:16 am
Agreed.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply