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:55 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Sunday, November 23, 2014 3:01 AM
Points: 470, Visits: 158
Hi,

It was a good question.

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


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 9:31 AM
Points: 717, Visits: 3,037
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: Wednesday, November 19, 2014 2:03 PM
Points: 2,278, Visits: 3,806
Good question.Thanks.

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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 8:05 AM
Points: 1,873, Visits: 518
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:05 PM
Points: 13,320, Visits: 12,804
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
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 616, Visits: 3,574
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: 2 days ago @ 3:02 AM
Points: 912, Visits: 1,500
Learned something new today. Thank you!

Best regards,

Andre Guerreiro Neto

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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 6,128, Visits: 8,393
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: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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
Posted Tuesday, March 27, 2012 8:56 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 616, Visits: 3,574
Hugo Kornelis (3/27/2012)
davidandrews13 (3/27/2012)

[quote]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?


i dont, no.
to create a view I right-click teh 'Views' node in Object Explorer and click 'New View...'. I then click to 'Close' the dialog box that asks me to Add Table and type my SELECT statement directly into the pain. when Saving it (by clicking the blue save disk) it expands the * to name all columns.

that's the reason why i thought it didn't matter if you specified SELECT *, or not - because it always expanded it out for me.


EDIT: actually, going back to the actual question, they do specifically create the view by running a CREATE View statement. so in that situation, you shouldn't do SELECT *. i guess its just that i've never created views in that manner!
Post #1273568
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse