473,837 Members | 1,838 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

select * in views

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
Apr 15 '06 #1
33 6670
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" <so*****@somepl ace.com> wrote in message
news:68******** *************** ****@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

Apr 15 '06 #2
Peter (so*****@somepl ace.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, es****@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
Apr 15 '06 #3
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 "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
Apr 15 '06 #4
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" <sr*****@ca.ibm .com> wrote in message
news:4a******** ****@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 "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

Apr 16 '06 #5
Perhaps we can get a hole of an MS developer by posting ms sql server
questions in comp.databases. ibm-db2.


:)
Apr 16 '06 #6
Peter (so*****@somepl ace.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, es****@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
Apr 16 '06 #7
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" <es****@sommars kog.se> wrote in message
news:Xn******** **************@ 127.0.0.1...
Peter (so*****@somepl ace.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, es****@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

Apr 16 '06 #8
Peter (so*****@somepl ace.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, es****@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
Apr 16 '06 #9
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_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/m...S,SQL.90).aspx
--

Apr 16 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2011
by: Carmine | last post by:
Is there anyway to execute this select statement with dbi? select 'grant select on ' concat substr(viewschema,1,7) concat '.' concat substr(viewname,1,18) concat ' to ' concat dbc_xid_user_id concat ';' from syscat.views, ls2user.vls_db_connection where viewschema = 'ls2user' and valid='Y' and seqno = 1
9
10774
by: Rowland Hills | last post by:
I have a table which is returning inconsistent results when I query it! In query analyzer: If I do "SELECT * FROM TABLE_NAME" I get no rows returned. If I do "SELECT COL1, COL2 FROM TABLE_NAME" I get 4 rows returned. In Enterprise manager:
6
2805
by: RCS | last post by:
I've been running into more and more complexity with an application, because as time goes on - we need more and more high-level, rolled-up information. And so I've created views, and views that use other views.. and the queries are getting slower and slower. This morning, I'm working on something like this: select <some columns>, "calculatedcolumn" = (select top 1 crap from stuff where
7
11924
by: CharlesEF | last post by:
Hi All, I have run into another problem that is eating my lunch. Should be simple but I am having one heck of a time. Please look at this SELECT statement: SELECT FROM States WHERE ] = "US"; is the SQL column name (because it sarts with 2?). As the statement is shown I get the error message: Unclosed quotation mark before the character
10
5644
by: serge | last post by:
Using "SELECT * " is a bad practice even when using a VIEW instead of a table? I have some stored procedures that are identical with the difference of one statement in the WHERE clause. If I create a single View and specify also in this View the WHERE clause that is common in these stored procedures, I will have the new stored procecures changed to be like:
5
10654
by: Paul Reddin | last post by:
Hi, using ROWNUMBER() is affecting the plan of a view very badly... is there a way of writing the following view to ensure rownumber() is done as the last thing done? i.e after the calling predicates have been applied to a_cte e.g I have rewritten the view as follows using a CTE, trying to
4
3021
by: serge | last post by:
I was working on figuring out where a certain application was storing the multiple selection choices I was doing through the app. I finally figured out that they were being store in an IMAGE data type colum with the variable length of 26 bytes. This is the first time I ran into such way of storing multiple selections in a single Image data type. Is this a better alternative than to store into a One-to-Many tables? If so then I'll have...
2
3251
by: Eitan | last post by:
Hello, I want a solutions for a compicateds sql select statments. The selects can use anything : views, stored procedures, analytic functions, etc... (not use materialized view, unless it is neccessary). question 1) The selects can use anything : views, stored procedures, analytic functions, etc...
1
4455
by: Patrick.Laprise | last post by:
Here's the thing. We have a SQL Server with more than 25 Databases on it. Each database is completely independant (but they all have the same structure) and is use to manage different project. We have more than 20000 logins split up between these databases (average between 800 and 1200 users per database). When users connect to the application, they have to specify the database they want to connect to and based on this, a list of...
0
9846
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9686
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10890
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10634
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10279
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9416
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
4479
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4053
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3127
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.