November 14, 2011 at 9:02 am
It boils down to "best practice". That means the only thing you have is what people in the industry prefer. There no scientific reason it is bad. It is the accumulation of experience of literally thousands, if not millions, of database professionals over decades that have formed this methodology.
Why is it bad as a programmer to name each variable as a single letter of the alphabet in sequential order? int a, char b, etc..
It doesn't take people blogging about how stupid that is to understand what a bad concept that is. This is no different.
There is no reason that select * will not work. It will work. The problem is in the reality of the system behind it. It will require a lot more effort of maintenance. The performance in general will be less (this can be proven). DDL changes may require recompiling code because of the new columns depending on how the code is written. Network usage and traffic is higher (this can also be proven).
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 14, 2011 at 9:03 am
Then as Cadavre did, use the developer needs and create a test case showing select * v. select explicit columns. This has been the most effective method for myself when showing a developer why not to use Select *.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 14, 2011 at 9:04 am
SQLRNNR (11/14/2011)
Then as Cadavre did, use the developer needs and create a test case showing select * v. select explicit columns. This has been the most effective method for myself when showing a developer why not to use Select *.
Add a case with a non unique NC index filter...
* vs columns will be clearer.
Then another with with blobs to drive it in further.
November 14, 2011 at 9:07 am
SanDroid (11/14/2011)
GSquared (11/14/2011)
SanDroid (11/14/2011)
Ninja's_RGR'us (11/14/2011)
You mean aside from ddl change, performance, network overuse, column order change, performance, indexes being useless, not seing the list of columns when working on the code?so aside from bad design that breaks the application and abysmal performance, you want more than that?
:hehe:
I agree with all those opinions, but unless I have public available reference material to go with your statement it is just an opinion and has less weight than the developer in question.
We're not talking about natural law here, we're talking about something that's going to be based on annecdotal material. Any "public available reference material" on this is just going to be that author's opinion on it. If it's an author the developer considers more knowledgeable than himself, that might help, but it'll still just be an opinion.
In my particular case (and many others), the opinion is based on extensive experience, and the weighing of known costs and benefits. But it's still "just an opinion".
On the other hand, disliking severe pain is also "just an opinion", and one that's based on personal experience. Some like it. Same difference here.
I can't believe that in 40+ years of RDBMS systems reference materials that none of them back up any of the post that have been made.
That pain hurts is a medical fact and backed up by literal TONs of literature and reference material on the neverous system. It is WAY beyond opinion.
You misread what I wrote. Pain, by definition, hurts. The two words are synonymous, it would be impossible for pain to not hurt. But what I said is that it's something I dislike. There are people who enjoy pain, or at least enjoy certain types/quantities of pain. Both the S&M crowd, and "no pain no gain" exercise types, are examples.
Same thing for this.
There is no absolute "using SELECT * will cause you to get zits" or anything like that which can be quantified, stated in terms of natural law or "by definition" structure. It's a question of which do you dislike more:
Having to spend a few extra seconds now putting in a list of column names, thus potentially avoiding significant future work
or
Spending a few less seconds now by using an implicit column list, potentially having to do a lot of future work to fix problems this causes
Personal preferences on readability of one vs the other is also a question of opinion. I prefer an explicit column list most of the time, because that is "self-documenting", but there are indeed times when the column list is long enough to be annoying. I always list them, for consistency, but I do recognize this is a personal preference.
You won't find something that can say, "Just like you cannot move faster than light, you must use an explicit column list in SELECT statements". If it does exist, the author was either being ignorant, humorous, or making some sort of point other than the one explicitly written.
The predominance of opinion amoungst people with long-term experience in this field is use explicit lists. But it is an opinion. The predominance of this can be confirmed easily through Google or Bing or whatever.
The thing to keep in mind is that "Earth being destroyed and everyone on it dying would be bad" is also an opinion. It's not a provable scientific fact. "Just an opinion" is a very misleading statement, is the point I'm making here.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 14, 2011 at 9:58 am
SanDroid (11/14/2011)
Please supply your reference in any answer you provide. For example a link to the internet or the name of the book that backs up your post.
For everyone that posted and followed these instructions - Thank you very much.
You are very very welcome and I appreciate you.
However, I am having to go with the reponses from another community Forum as a reference for this issue.
For G Squared -
I did ask please in the original post and even responded directly and gave a reason why I wanted this on the posts here.
Thanks so much for posting your, quoting my boss; "Off Topic tactless comments about pain that borders on a rant" with the other reply's here.
I hope you understand how much they helped the credbility of what the other posters said.
I no longer need or will be reading any responses to this thread here. Thank you again for your support.
November 14, 2011 at 10:03 am
and tonight's 100 M lottery numbers are!
:hehe:
November 14, 2011 at 10:20 am
SanDroid (11/14/2011)
SanDroid (11/14/2011)
Please supply your reference in any answer you provide. For example a link to the internet or the name of the book that backs up your post.For everyone that posted and followed these instructions - Thank you very much.
You are very very welcome and I appreciate you.
However, I am having to go with the reponses from another community Forum as a reference for this issue.
For G Squared -
I did ask please in the original post and even responded directly and gave a reason why I wanted this on the posts here.
Thanks so much for posting your, quoting my boss; "Off Topic tactless comments about pain that borders on a rant" with the other reply's here.
I hope you understand how much they helped the credbility of what the other posters said.
I no longer need or will be reading any responses to this thread here. Thank you again for your support.
To each his own.
I tried to point out that even commonly held opinions, and even those held by people with authority on the subject at hand, are still simply opinions. I appologize if my choice of a humorous/colorful subject for that somehow makes that a "rant". (Which isn't clear to me.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 14, 2011 at 12:07 pm
It's too bad nobody referenced BOL for this quote:
"If a SELECT is used in an application or script that has logic dependent on the number of columns in the result set, it is better to specify all the columns in the select list instead of specifying an asterisk. If columns are later added to the table or views referenced by the SELECT statement, the application is shielded from the change if the columns were listed individually. If an asterisk (*) was specified, the new columns become a part of the result set and may affect the logic of the application or script. You should avoid using the asterisk (*), especially against catalog views, dynamic management views, and system table-valued functions. Future upgrades and releases of Microsoft SQL Server may add columns and change the order of columns to these views and functions. These changes may break applications that expect a particular order and number of columns."
http://msdn.microsoft.com/en-us/library/ms189287(v=SQL.100).aspx
Greg
November 14, 2011 at 12:22 pm
Greg Charles (11/14/2011)
It's too bad nobody referenced BOL for this quote:"If a SELECT is used in an application or script that has logic dependent on the number of columns in the result set, it is better to specify all the columns in the select list instead of specifying an asterisk. If columns are later added to the table or views referenced by the SELECT statement, the application is shielded from the change if the columns were listed individually. If an asterisk (*) was specified, the new columns become a part of the result set and may affect the logic of the application or script. You should avoid using the asterisk (*), especially against catalog views, dynamic management views, and system table-valued functions. Future upgrades and releases of Microsoft SQL Server may add columns and change the order of columns to these views and functions. These changes may break applications that expect a particular order and number of columns."
http://msdn.microsoft.com/en-us/library/ms189287(v=SQL.100).aspx
Which is exactly what all of already said.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 14, 2011 at 4:04 pm
GSquared (11/14/2011)
Greg Charles (11/14/2011)
It's too bad nobody referenced BOL for this quote:"If a SELECT is used in an application or script that has logic dependent on the number of columns in the result set, it is better to specify all the columns in the select list instead of specifying an asterisk. If columns are later added to the table or views referenced by the SELECT statement, the application is shielded from the change if the columns were listed individually. If an asterisk (*) was specified, the new columns become a part of the result set and may affect the logic of the application or script. You should avoid using the asterisk (*), especially against catalog views, dynamic management views, and system table-valued functions. Future upgrades and releases of Microsoft SQL Server may add columns and change the order of columns to these views and functions. These changes may break applications that expect a particular order and number of columns."
http://msdn.microsoft.com/en-us/library/ms189287(v=SQL.100).aspx
Which is exactly what all of already said.
Here is an example of how easily you can get a wrong result from a view with "select *" or even an error
set nocount on
if object_ID('dbo.ColumnOrder') is not null drop table dbo.ColumnOrder
create table dbo.ColumnOrder(ID1 int ,ID2 int)
insert into dbo.ColumnOrder(ID1, ID2) values(1,2)
go
if object_ID('dbo.ColumnOrderView') is not null drop view dbo.ColumnOrderView
go
create view dbo.ColumnOrderView
As
select * from dbo.ColumnOrder
go
-- 1st select from the view
select * from ColumnOrderView
go
ALTER TABLE dbo.ColumnOrder ADD ID3 int
ALTER TABLE dbo.ColumnOrder DROP COLUMN ID1
-- 2nd select from the view
select * from ColumnOrderView
go
ALTER TABLE dbo.ColumnOrder DROP COLUMN ID2
go
-- 3rd select from the view
select * from ColumnOrderView
go
drop view dbo.ColumnOrderView
drop table dbo.ColumnOrder
ID1 ID2
----------- -----------
1 2
ID1 ID2
----------- -----------
2 NULL
Msg 4502, Level 16, State 1, Line 3
View or function 'ColumnOrderView' has more column names specified than columns defined.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply