Posted by Erland Sommarskog on 04/16/06 13:54

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

[Back to original message]