SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


VIEWS 4


VIEWS 4

Author
Message
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17313 Visits: 7421
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”
khelloufsofiane 6183
khelloufsofiane 6183
SSChasing Mays
SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)

Group: General Forum Members
Points: 611 Visits: 179
Hi,

It was a good question.

Thenks.
Rich Mechaber
Rich Mechaber
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2779 Visits: 3671
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
M&M
M&M
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6131 Visits: 3913
Good question.Thanks.

M&M
Michael Riemer
Michael Riemer
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3116 Visits: 656
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 *!
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63652 Visits: 17969
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. :-P

_______________________________________________________________

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 Modens 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)
davidandrews13
davidandrews13
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1929 Visits: 4641
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.
Andre Guerreiro
Andre Guerreiro
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2293 Visits: 1515
Learned something new today. Thank you!

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18967 Visits: 12426
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
bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16005 Visits: 25280
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search