| Groups | Blog | Home |
sql server misc : 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 [quoted text, click to view] "Peter" <someone@someplace.com> wrote in message People are telling me it is bad to putnews:6850b$44417233$50394e09$25293@news.chello.nl... 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
[quoted text, click to view]
Peter wrote: There are two reasons speaking against select * in views and select * in > 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? 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
Peter (someone@someplace.com) writes:
[quoted text, click to view] > 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/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
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. [quoted text, click to view] "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
Peter (someone@someplace.com) writes:
[quoted text, click to view] > 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
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 [quoted text, click to view] "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
Peter (someone@someplace.com) writes:
[quoted text, click to view] > 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/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
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
Perhaps we can get a hole of an MS developer by posting ms sql server
questions in comp.databases.ibm-db2. :)
[quoted text, click to view]
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/ms130214(en-US,SQL.90).aspx --
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |