|
|
Posted by Peter on 04/16/06 14:36
Erland,
Thanks for your reply.
The views are a interface to another database, or are a interface to the
database where they live. To make things can change in the underlaying
table, for example, to put a restriction on rows on them or to place them in
another database.
The concept is the views returns the same columns as the underlaying tables.
In one case, some tables are in different databases, within another
database, those tables are all available by views. The transaction tables
are in its own database. Reference tables are in others.
The problem I have with this solution is I can not make indexed views on the
interface views.
The way I look at it is that when the implementation of SQL Server changes
in a next version, so the select * causes problems, I can change that anyway
and replace the asterix with the column names. I then make the cost of extra
maintenance overhead when nessecary. In the mean time, I didnt put any extra
development time in naming each column. When something change in the table,
I just recompile the view, without worrying about which columns are added.
Let me put my question in another way:
Is there an extra performance overhead now in SQL Server 2000/2005 when I
use select * in views instead of naming each column of the table?
Peter
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns97A78354BFB8CYazorman@127.0.0.1...
> Peter (someone@someplace.com) writes:
>> I see you are a IBM expert. Is your story true for DB2 only or do you
>> know
>> it is true for Microsoft SQL Server 2000 too?
>>
>> Because, if i do a select on a view (select * from <atable>) with only a
>> projection (only a few colums in the select list), I see in the
>> execution plan a clustered index scan with a minimized returned data
>> row size. Also, when I use a where clause on a indexed column, I see an
>> index seek in the query plan. Also, in the query plan, I dont see view
>> names, but only table names. It seems to me it works like documented by
>> Microsoft, in the execution plan, the view is replaces by the underlying
>> view logics, but only what is nessecary.
>>
>> Indeed, when I add a new column to the underlying table, the view is not
>> changed until I recompile the view. What is the problem with that if
>> existing code never do a select * on the view? And if existing code does
>> a
>> select *, then, I think this code wants all columns. So it is better to
>> recompile.
>
> While Serge has more experienc of DB2 than SQL Server, I don't think his
> observations are out of whack. The current implementation may forgive you,
> but the next may not.
>
> Since I use views very rarely overall muyself, I'm not sure why people are
> so keen on using SELECT * in views.
>
> I can think of two cases where it makes sense to use SELECT * in a view
> definition:
> 1) The view presents a subset of table for row-level security.
> 2) The view is logically a table, that is implemented as several. That is,
> partitioned views.
>
> Then again, since you have to refresh the view when you change the
> underlying tables, you could just as well update the source code for
> it as well.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|