SEO

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

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@someplace.com> wrote in message
news:6850b$44417233$50394e09$25293@news.chello.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

Peter (someone@someplace.com) writes:
> I dont know exactly why but some say:
>
> A select * from makes sql server does a table scan.
>
> Is that true,

No. A query like:

SELECT * FROM sometable WHERE primarykey = 12

will use the index on PK.


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

Peter wrote:
> 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?
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 "conservative" 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

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:4adcb8Fs7njpU1@individual.net...
> Peter wrote:
>> 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?
> 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 "conservative" 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

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

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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

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/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx

Peter (someone@someplace.com) writes:
> 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?

No.

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

Peter wrote:
>
> 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?

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_recompile '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/m...S,SQL.90).aspx
--