October 2, 2008 at 5:41 am
This is really another subject, but I feel I have to disagree anyway.
What you're essentially proposing here, is that the model doesn't count.
Whenever you're using 'select *' instead of an explicit list where you state the intended
columns to be returned, you're not only leaving the code undocumented for the next reader,
you're also saying 'I don't give a cr*p what this may return now, or in the future'.
'If it changes, let "them" handle it'.
So, I have to disagree that 'select *' is useful or warranted anywhere except in an EXISTS clause.
Anywhere else, it's usage is just lazy and irresponsible.
My .02 ofc 😉
/Kenneth
October 2, 2008 at 6:00 am
Animal Magic (10/2/2008)
Jim, i could be wrong, but i think the debate is regarding the number of rows returned rather than being able to see all the columns.
Nope, you are not wrong, I am. I scanned the earlier posts too quickly, and thought the "*" was falling under attack again.
This topic (now that I read it more carefully) relates to an thread I started a while back asking how to limit the impact of rogue (or long running) queries. The response back then really surprised me, in that most suggested just restricting queries against the production server.
(That thread is: http://www.sqlservercentral.com/Forums/Topic487071-146-1.aspx )
There is, perhaps, some hope in SS 2008, via Resource Governor.
In the meantime, I am going to study Andrew Gothard's solution, which looks to me like it has promise.
October 2, 2008 at 6:57 am
Kenneth Wilhelmsson (10/2/2008)
This is really another subject, but I feel I have to disagree anyway.What you're essentially proposing here, is that the model doesn't count.
Whenever you're using 'select *' instead of an explicit list where you state the intended
columns to be returned, you're not only leaving the code undocumented for the next reader,
you're also saying 'I don't give a cr*p what this may return now, or in the future'.
'If it changes, let "them" handle it'.
So, I have to disagree that 'select *' is useful or warranted anywhere except in an EXISTS clause.
Anywhere else, it's usage is just lazy and irresponsible.
My .02 ofc 😉
/Kenneth
There's also the total maintenance nightmare of how much stuff is suddenly going to break when you change the structure of the table by adding a column in future if you're using *, alongside the performance issues with returning guff you've no intention of using or don't actually need.
October 2, 2008 at 10:04 am
Kenneth Wilhelmsson (10/2/2008)
This is really another subject, but I feel I have to disagree anyway.What you're essentially proposing here, is that the model doesn't count.
Whenever you're using 'select *' instead of an explicit list where you state the intended
columns to be returned, you're not only leaving the code undocumented for the next reader,
you're also saying 'I don't give a cr*p what this may return now, or in the future'.
'If it changes, let "them" handle it'.
So, I have to disagree that 'select *' is useful or warranted anywhere except in an EXISTS clause.
Anywhere else, it's usage is just lazy and irresponsible.
/Kenneth
Generally I agree with you but I do use SELECT * when moving rows from a main table to an archive table as it will produce an error, rather than loose columns, if the table structures become different.
October 2, 2008 at 10:10 am
Ken McKelvey (10/2/2008)
Kenneth Wilhelmsson (10/2/2008)
This is really another subject, but I feel I have to disagree anyway.What you're essentially proposing here, is that the model doesn't count.
Whenever you're using 'select *' instead of an explicit list where you state the intended
columns to be returned, you're not only leaving the code undocumented for the next reader,
you're also saying 'I don't give a cr*p what this may return now, or in the future'.
'If it changes, let "them" handle it'.
So, I have to disagree that 'select *' is useful or warranted anywhere except in an EXISTS clause.
Anywhere else, it's usage is just lazy and irresponsible.
/Kenneth
Generally I agree with you but I do use SELECT * when moving rows from a main table to an archive table as it will produce an error, rather than loose columns, if the table structures become different.
Ok, but that's more along the lines of using a cursor for quick and dirty admin tasks. Even I do that on occasion - even though I'd gnaw off my own arm rather than use one in production code
October 2, 2008 at 10:50 am
Why not walk around with a big stick and lay the smack down on people who do that? :w00t:
October 2, 2008 at 12:55 pm
Jim Russell (10/2/2008)
Before everyone jumps on the "never SELECT *" bandwagon, I'd argue that there are cases where * is both useful and appropriate. One example is a view that provides a row-filtered subset of a table; if columns are added to the table (that's one of the advantages of a relational table, right?) it is cleaner (more generalized) to have the view reflect the current column composition of the table, as opposed to "hard coding" in the columns that existed in the "old days". Strikes me that hard coding column names is not far removed from hard coding character positions in a flat file, or worse, hard coding row numbers.)(OK, the view may need to be refreshed, but I think that is an artifact of the current SQL Server implementation.)
Actually I disagree with your example. As you already mentioned when you use select * in a view, it will not catch new column that will be added to the table that the view is based on until you refresh the view. In my opinion this is not an advantage. When I see the select clause of the view I want to know exactly which columns will be sent to the client and not be depended on the time that the view was created or refreshed.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 3, 2008 at 8:09 am
You are searching for (and being offered) technical solutions to a human-resource management issue.
The novice developers mostly need training/coaching/mentoring/feedback.
They need to know why it's bad to bog down the servers with large queries.
They need opportunities to learn and practice and see differences in results in their DEV environment.
They could be asked to review each others' code and be given incentives/rewards for increasing efficiencies.
Also it sounds like they need a DEV environment with more regular refreshes of live data.
Positive feedback typically works better than negative (on humans at least).
October 5, 2008 at 5:17 am
I can't think of a situation where any developer needs to have full access to a production database. Developers should never, under any circumstances, for any reason, have full access to production data, expecially with their everyday ID. That's begging for irreversible damage to production data and code (not to mention the possibility of malicious code ebing introduced). Nothing should ever be moved to production without appropriate change management and documentation. I don't care if users are screaming their heads off that it's an emergency. Do the change management. This also keeps you in good standing with your auditors when it comes time for a check on Sarbanes-OXley compliance.
October 9, 2008 at 6:38 am
Who said developers have full access to production? Doing a select * only takes read access.
Thank you all for your replies. I have "coached" the offending developers and will periodically run profiler filtered on like 'select *%' queries to see if they continue. If so, I'll go with more extreme measures like the set rowcount logon trigger.
October 9, 2008 at 6:55 am
Vincent Central (10/9/2008)
Who said developers have full access to production? Doing a select * only takes read access.Thank you all for your replies. I have "coached" the offending developers and will periodically run profiler filtered on like 'select *%' queries to see if they continue. If so, I'll go with more extreme measures like the set rowcount logon trigger.
Why not just look for the star? If you’ll look for %select *% then you won’t be able to find something like select * (notice that there is more then one space between the select and the * symbol), or select getdate(),* or any other combination that involves using the start in the select statement.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 9, 2008 at 7:57 am
Ok, so I'm lazy and when doing some ad hoc work on small tables I used to occasionally use Select * WHERE 1=2 to get a list of column names or perhaps select top 10 to get some data to look at. Mostly this was when working on a very poorly documented system that I was attempting to document. That said I probably haven't used select * for quite a while for these purposes mostly because of red Gate's SQL Prompt. to get a column list I just start typing and away I go. If I need to know more, the table summary is right there.
Granted I'll occasionally still use select * followed by Tab if I need 9 columns out of a 10 column list, cause SQL prompt will type out all ten, then I just need to delete the one I don't need.
Yes this is searching for technical solutions to an HR/management issue, but I find that spending the limited amount of cash for SQL prompt has been very worthwhile in my organization.
-Luke.
October 9, 2008 at 6:47 pm
Vincent Central (10/9/2008)
Who said developers have full access to production? Doing a select * only takes read access.Thank you all for your replies. I have "coached" the offending developers and will periodically run profiler filtered on like 'select *%' queries to see if they continue. If so, I'll go with more extreme measures like the set rowcount logon trigger.
Heh... you might want to change that filter a bit, Vincent... they'll eventually figure out that SELECT * or SELECT* or (space)SELECT * will avoid your current profiler filter. 😉 Try using '%SELECT%*%' instead...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 9, 2008 at 7:22 pm
Jeff Moden (10/9/2008)
Vincent Central (10/9/2008)
Who said developers have full access to production? Doing a select * only takes read access.Thank you all for your replies. I have "coached" the offending developers and will periodically run profiler filtered on like 'select *%' queries to see if they continue. If so, I'll go with more extreme measures like the set rowcount logon trigger.
Heh... you might want to change that filter a bit, Vincent... they'll eventually figure out that SELECT * or SELECT* or (space)SELECT * will avoid your current profiler filter. 😉 Try using '%SELECT%*%' instead...
Wouldn't just %*% work? Or would that pull back too much?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 9, 2008 at 7:28 pm
That would grab /*__*/ if it's on the same line as some code of embedded within a statement.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply