select * in views

[Peter]

People are telling me it is bad to put

select * from <atable>

in a view. I better should list all fields of the table inside the
definition of the view.

I dont know exactly why but some say:

A select * from makes sql server does a table scan.

Is that true, even if i put a where on the select on the view? And what if i
dont list all fields in the select on the view?

Thanks for the answer.

Peter

[Tom Moreau]

Re: select * in views

Try the following code:

create table t
(
ID int primary key
, Col1 int not null
)
go

create view v
as
select * from t
go

insert v values (1, 2)
go

alter table t
add
Col2 int not null constraint CK_t default (0)
go

alter table t
drop constraint CK_t
go

select * from v
go
select * from t
go

insert v values (2, 2)
go

drop view v
drop table t



You'll see that the SELECT from the view did not pick up the extra column.
Also, the second insert failed - even though the SELECT on the view
suggested there were only 3 columns.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada

"Peter" <someone@somepl ace.com> wrote in message
news:6850b$4441 7233$50394e09$2 5293@news.chell o.nl...
People are telling me it is bad to put

select * from <atable>

in a view. I better should list all fields of the table inside the
definition of the view.

I dont know exactly why but some say:

A select * from makes sql server does a table scan.

Is that true, even if i put a where on the select on the view? And what if i
dont list all fields in the select on the view?

Thanks for the answer.

Peter

[Erland Sommarskog]

Re: select * in views

Peter (someone@somepl ace.com) writes:[color=blue]
> I dont know exactly why but some say:
>
> A select * from makes sql server does a table scan.
>
> Is that true,[/color]

No. A query like:

SELECT * FROM sometable WHERE primarykey = 12

will use the index on PK.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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

[Serge Rielau]

Re: select * in views

Peter wrote:[color=blue]
> People are telling me it is bad to put
>
> select * from <atable>
>
> in a view. I better should list all fields of the table inside the
> definition of the view.
>
> I dont know exactly why but some say:
>
> A select * from makes sql server does a table scan.
>
> Is that true, even if i put a where on the select on the view? And what if i
> dont list all fields in the select on the view?[/color]
There are two reasons speaking against select * in views and select * in
general.
1. Typically your app does not need all columns.
So by using select * you will:
a) flow wider rows than needed
b) force the DBMS to access the data page even if, in reality a mere
index access would have been sufficient. Once you access the data page
anyway the optimizer will be tempted to use more table scans, but that's
really secondary damage.
Within teh context of a view you will force the optimizer to do more
work than needed. I.e. it needs to drop unused columns which may or may
not work depending on teh capabilities of the DBMS.

2. When a column is added to the table after the view is created the SQL
standard required "conservati ve" semantics. That is teh existing view
will not pick up the new column.
If however you drop and recreate the view (for whatever reason) the view
will pick up the new column. This can cause some rather unexpected
behavior which may be hard to debug.
The select * in a way is a time bomb.

I use SELECT * as a convenience for throw-away, ad-hoc queries, but not
for anything related to a production system.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Peter]

Re: select * in views

Serge,

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.

Do I oversee something? Please give a reaction if I am right or I oversee
something.

"Serge Rielau" <srielau@ca.ibm .com> wrote in message
news:4adcb8Fs7n jpU1@individual .net...[color=blue]
> Peter wrote:[color=green]
>> People are telling me it is bad to put
>>
>> select * from <atable>
>>
>> in a view. I better should list all fields of the table inside the
>> definition of the view.
>>
>> I dont know exactly why but some say:
>>
>> A select * from makes sql server does a table scan.
>>
>> Is that true, even if i put a where on the select on the view? And what
>> if i dont list all fields in the select on the view?[/color]
> There are two reasons speaking against select * in views and select * in
> general.
> 1. Typically your app does not need all columns.
> So by using select * you will:
> a) flow wider rows than needed
> b) force the DBMS to access the data page even if, in reality a mere
> index access would have been sufficient. Once you access the data page
> anyway the optimizer will be tempted to use more table scans, but that's
> really secondary damage.
> Within teh context of a view you will force the optimizer to do more work
> than needed. I.e. it needs to drop unused columns which may or may not
> work depending on teh capabilities of the DBMS.
>
> 2. When a column is added to the table after the view is created the SQL
> standard required "conservati ve" semantics. That is teh existing view will
> not pick up the new column.
> If however you drop and recreate the view (for whatever reason) the view
> will pick up the new column. This can cause some rather unexpected
> behavior which may be hard to debug.
> The select * in a way is a time bomb.
>
> I use SELECT * as a convenience for throw-away, ad-hoc queries, but not
> for anything related to a production system.
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab[/color]

[Steve Dassin]

Re: select * in views

Perhaps we can get a hole of an MS developer by posting ms sql server
questions in comp.databases. ibm-db2.




:)

[Erland Sommarskog]

Re: select * in views

Peter (someone@somepl ace.com) writes:[color=blue]
> 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.[/color]

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@sommarsk og.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

[Peter]

Re: select * in views

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@sommars kog.se> wrote in message
news:Xns97A7835 4BFB8CYazorman@ 127.0.0.1...[color=blue]
> Peter (someone@somepl ace.com) writes:[color=green]
>> 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.[/color]
>
> 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@sommarsk og.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx[/color]

[Erland Sommarskog]

Re: select * in views

Peter (someone@somepl ace.com) writes:[color=blue]
> 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?[/color]

No.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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

[David Portas]

Re: select * in views

Peter wrote:[color=blue]
>
> 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?[/color]

If the queries are executed by SQL Server on the same server as the
view then there may not be any measurable performance overhead of
SELECT * (except perhaps during compilation). However, you are mistaken
about something else. Recompiling a view containing SELECT * will NOT
necessarily cause it to reflect changes made to the base tables. For
this reason alone, it is a bad idea to use SELECT * in views. See the
following example, which was tested on 2000 SP4 and 2005.

CREATE TABLE dbo.t1 (x INT NOT NULL PRIMARY KEY, z1 INT NULL);
GO
CREATE VIEW dbo.v1 AS
SELECT * FROM dbo.t1
GO
ALTER TABLE dbo.t1 DROP COLUMN z1 ;
ALTER TABLE dbo.t1 ADD z2 INT ;
GO
EXEC dbo.sp_recompil e 'dbo.v1' ;
/* Notice that the second column still exists as Z1 in the view */
SELECT x,z1 FROM dbo.v1 ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

[Serge Rielau]

Re: select * in views

Well, first of all IBM is not a product. I could very well be an IBM SQL
Server expert (working for IGS or Websphere for example), but I am a DB2
expert, which I take it was what you meant. :-)

My comments were generic and I'm confident they apply to any SQL RDBMS
out there.

Couple of observations:
1. A thumb rule for using TBSCAN over (index scan & page fetch) is when
the selectivity is worse than, say 5%. Sure any DBSM will pick it's own
secret number, but I don't doubt for a moment that SQL Server, having a
cost based optimizer will eventually say: "Crap. You make me go after
columns not in my index and I'm going to touch next to at least one row
in any page anyway!".
Enabling queries to use covering indexes is important. SELECT * is
counter productive on that end.

2. If all you did in you experiment was a simple CREATE VIEW V AS SELECT
* FROM T then this doesn't say too much.
Thsi is way I made my statement relative. If you SQL is more complex
then mileage will vary depending e.g. on the version of your RDBMS since
you are relying on the optimizer.
SQL is about:
You tell the RDBMS WHAT you want. The RDBMS takes care of the HOW to get it.
As developers it is our responsibility to specify the WHAT correctly.

I'm referring back to the view vs. stored procedure thread here. In that
thread users didn't trust views at all. I don't go that far, but I
wouldn't trust any optimizer ( SQL Server, DB2, you name it) to fix up
all my lazyness through divine intuition.

Lastly I respectfully disagree with the subsequent note in this thread
that over specification of columns in a view has no performance impact.
While this may be true in most cases once the statement invoking the
view is compiled, you are relying on the compiler to optimize out the
unused columns. And that is code path and costs you CPU on first
compile. Whether you can actually "feel" that depends on cache friendly
your app is.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Erland Sommarskog]

Re: select * in views

Serge Rielau (srielau@ca.ibm .com) writes:[color=blue]
> Lastly I respectfully disagree with the subsequent note in this thread
> that over specification of columns in a view has no performance impact.[/color]

I think Peter's question actually was whether there was a performance
impact of saying "SELECT *" instead of listing all columns in the table
explicitly, and for all my dislike for SELECT * in production code, I
can't think of any reason why SELECT * should be any more expensive in
SQL Server.

I completely agree with that best is to include exactly those columns
for which there is an actual need.

One problem I often fight at work is that I want to drop a column, or
drastically change the meaning of it. I suspect that it is not really
in use, but still I find a bunch of stored procedures that return this
column in a result set. But I can also see that these procedures aim
at returning the universe, so I have no idea whether the value is used
for something. (Usually, I end up dropping the column anyway.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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

[Alexander Kuznetsov]

Re: select * in views

Serge Rielau wrote:[color=blue]
> 1. A thumb rule for using TBSCAN over (index scan & page fetch) is when
> the selectivity is worse than, say 5%. Sure any DBSM will pick it's own
> secret number,[/color]

Serge,

I think that threshold selectivity is not a constant - for SQL Server
it also depends on the width of bookmarks. So, if bookmarks are 4 byte
integers, the threshold selectivity is lower than if bookmarks are
50-byte character fields.

Similarly, besides selectivity DB2 also considers clustering factor,
and if an index has a high clustering factor, even low 50% selectivity
might be good enough for an access via an index, correct?

[Peter]

Re: select * in views

>[color=blue]
> CREATE TABLE dbo.t1 (x INT NOT NULL PRIMARY KEY, z1 INT NULL);
> GO
> CREATE VIEW dbo.v1 AS
> SELECT * FROM dbo.t1
> GO
> ALTER TABLE dbo.t1 DROP COLUMN z1 ;
> ALTER TABLE dbo.t1 ADD z2 INT ;
> GO
> EXEC dbo.sp_recompil e 'dbo.v1' ;
> /* Notice that the second column still exists as Z1 in the view */
> SELECT x,z1 FROM dbo.v1 ;
>
> --[/color]

O that is bad!!! I gonne test that immidiatly tuesday. Is it different when
naming the columns?

[Peter]

Re: select * in views


"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns97A7C00 123E9EYazorman@ 127.0.0.1...[color=blue]
> Serge Rielau (srielau@ca.ibm .com) writes:[color=green]
>> Lastly I respectfully disagree with the subsequent note in this thread
>> that over specification of columns in a view has no performance impact.[/color]
>
> I think Peter's question actually was whether there was a performance
> impact of saying "SELECT *" instead of listing all columns in the table
> explicitly, and for all my dislike for SELECT * in production code, I
> can't think of any reason why SELECT * should be any more expensive in
> SQL Server.
>
> I completely agree with that best is to include exactly those columns
> for which there is an actual need.
>
> One problem I often fight at work is that I want to drop a column, or
> drastically change the meaning of it. I suspect that it is not really
> in use, but still I find a bunch of stored procedures that return this
> column in a result set. But I can also see that these procedures aim
> at returning the universe, so I have no idea whether the value is used
> for something. (Usually, I end up dropping the column anyway.)
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx[/color]

Erland,

In my case, the views only virtualizes the tables. The views must have the
same structure as the table. So if there are colums added to the table, the
view needs to be updated to. In that case, you have the same problem as the
table without view. You want to delete a column but you dont know if it is
used anywere.

What I mean is, declaring the view as select * is exactly what I functional
want. Give me all columns of the table, no matter what columns there are.

So, the statement

SELECT * FROM sometable

comes closer to what I want and what I mean than

SELECT
column1,
column2,
column3,
column4
FROM
sometable