Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

VIEWS 4 Expand / Collapse
Author
Message
Posted Tuesday, March 27, 2012 5:15 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:03 PM
Points: 3,915, Visits: 5,105
Great question, Ron
thanks

As to who creates views with "SELECT * FROM...":
we recently had a vendor app breaking due to schema changes to the underlying tables (an "upgrade" by the vendor), where the relevant forms use views to reflect data.

upon investigation, we found "SELECT *..." in almost all these views...


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1273393
Posted Tuesday, March 27, 2012 5:55 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 7:17 AM
Points: 439, Visits: 151
Hi,

It was a good question.

Thenks.
Post #1273411
Posted Tuesday, March 27, 2012 5:56 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:01 PM
Points: 687, Visits: 3,002
I had a hard time finding documentation on this the first time I ran across it, though I'm sure someone will post a relevant link from BOL. What I did find was this helpful note about Sybase, and I guess not much has changed in this regard since the Sybase days:

"However, if you alter the structure of a view's underlying table by adding columns,
the new columns will not appear in a view that is defined with a select * clause unless the view is dropped and redefined.
This is because the asterisk in the original view definition considers only the original columns. "

http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/28420;pt=28336

Thanks for the question,
Rich
Post #1273413
Posted Tuesday, March 27, 2012 7:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:52 PM
Points: 2,270, Visits: 3,789
Good question.Thanks.

Mohammed Moinudheen
Post #1273493
Posted Tuesday, March 27, 2012 7:41 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:11 AM
Points: 1,752, Visits: 476
Luckily I re-read and checked this a few times. First pass reading I thought it said deleted a row! Couldn't understand what that would really be checking!
As a number of posts say, never use Select *!
Post #1273501
Posted Tuesday, March 27, 2012 7:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:16 PM
Points: 13,301, Visits: 12,159
Nice back to basics question. It seems from the discussion that this is still not as widely known as I would expect. For the first time in quite a long time I knew what the answer was before I even finished the first sentence.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1273512
Posted Tuesday, March 27, 2012 8:44 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 10:00 AM
Points: 575, Visits: 3,392
Carlo Romagnano (3/27/2012)
Easy question!
Good practice is never use "SELECT * FROM" in view, too.


surely it doesn't matter if you do SELECT *?
every time i create or update a view to '*' instead of naming each individual column name, it expands it out to the individual column names anyway.

and if you do want to bring back all columns, type * and let it expand out all the column names itself instead of manually typing them yourself.
Post #1273545
Posted Tuesday, March 27, 2012 8:45 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 21, 2014 5:59 AM
Points: 897, Visits: 1,488
Learned something new today. Thank you!

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2
Post #1273547
Posted Tuesday, March 27, 2012 8:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:20 PM
Points: 5,975, Visits: 8,236
davidandrews13 (3/27/2012)
surely it doesn't matter if you do SELECT *?


It definitely matters. In the case of this question, you get an error either way. But in an earliers message, I posted a script where, after making some changes in the table, the view produces unexpected (no, a better way to word that is "incorrect") results, without giving an error or even warning message. Replace the * with a column-list in that code, and you'll get an error message. Now, getting an error unexpectedly after changing a table might suck - but not nearly as much as the damage that ccan be caused by a view returning incorrect results without you being alerted to it!

every time i create or update a view to '*' instead of naming each individual column name, it expands it out to the individual column names anyway.

That's not standard behaviour. Do you have Red Gate's SQLPrompt installed? Or another, similar product?



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1273561
Posted Tuesday, March 27, 2012 8:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:42 PM
Points: 5,589, Visits: 24,969
davidandrews13 (3/27/2012)
Carlo Romagnano (3/27/2012)
Easy question!
Good practice is never use "SELECT * FROM" in view, too.


surely it doesn't matter if you do SELECT *?
every time i create or update a view to '*' instead of naming each individual column name, it expands it out to the individual column names anyway.

and if you do want to bring back all columns, type * and let it expand out all the column names itself instead of manually typing them yourself.


Ah but if you drop a column from the underlying table, then SELECT * will not perform properly. So yes it does matter.


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1273566
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse