November 4, 2009 at 9:44 pm
Comments posted to this topic are about the item Preventing usage of "SELECT *..."
November 5, 2009 at 2:59 am
Interesting but i feel its not practical to add dummy columns to tables. 🙂
"Keep Trying"
November 5, 2009 at 3:55 am
Another solution that works well in most situations is to simply avoid allowing users to execute SQL statements at all - ensure that all access to tables is performed via stored procedures.
November 5, 2009 at 4:01 am
We have similiar issues here with the use of *, espically with young developers.
As all our DB projects are under source control, ive been able to control checking in SQL containing * using Code Analysis and Check in policies.
Regards
Gary Howlett
November 5, 2009 at 4:25 am
November 5, 2009 at 4:30 am
In interesting solution, for a not so real problem, as any decent organisation has standards in place and will ensure that all code conforms to the corporate standards.
If not the developer resonsible needs to rewrite the code in line with the standards, they eventually learn that they cant write Selcet * as they have to do double the work.
Besides with the 2008 GDR, you can enforce these at a code entry level.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 5, 2009 at 5:14 am
I think you can still use 'select anyfield, tblname.* from tblname' though.
November 5, 2009 at 5:16 am
I love posts like this. Sure, the specific example used may not be practical, but I just like the thinking behind coming up with the solution. Often that in itself can open up other avenues of thought that would otherwise have remained hidden.
November 5, 2009 at 5:30 am
May not be practical.
May not be standard.
But it is a good/smart solution.
🙂
Regards.
November 5, 2009 at 5:41 am
But it does not solve the problem when I can use
select tblname.* from tblname
with the same result.
November 5, 2009 at 5:54 am
Exactly why is "select * " not a good idea? I searched for articles with that tag but came up empty? I can't really see any reason not to do so, as long as you prefix with tablenames/aliases. And by the term "users" does the article author mean developers or end users ?
November 5, 2009 at 6:16 am
A good example of something that would be better solved through education. The cost in usability and complication just doesn't seem worth it. The time sorting out the occasional problem that might arise from having "SELECT *..." is surely a smaller cost than maintaining and living with the above solution.
"SELECT *.." is always useful when exploring databases, I think most DBAs/Developers would find it very painful to not be able to have a quick look at the data!
Edit:
sorte.orm (11/5/2009)
Exactly why is "select * " not a good idea
The point is that "*" changes. This can be bad if other processes expect a fixed dataset. Adding a field to a table suddenly breaks x, y and z. But like I said above, the times this has tripped me up and the time it takes to resolve are so minimal that it is not really a worry. Although perhaps it depends on the nature of your system? And as below, using * also sends more data than may be necessary.
November 5, 2009 at 6:22 am
If you're not actually uising all the columns it also means you're returning a lot more data to the calling application than it actually needs. That's not so much of an issue if you're returning half-a-dozen rows, but if you're retrieving thousands of them, all those extra bytes add up to a lot greater network load on your server!
November 5, 2009 at 6:25 am
sorte.orm (11/5/2009)
Exactly why is "select * " not a good idea? I searched for articles with that tag but came up empty? I can't really see any reason not to do so, as long as you prefix with tablenames/aliases. And by the term "users" does the article author mean developers or end users ?
SELECT * is pretty tough on correct index usage and violates the performance notion of returning only what you need (ie: be kind to the "pipe"). There are also some reasons from the GUI side of the house in the following short article...
http://www.adopenstatic.com/FAQ/selectstarisbad.asp
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2009 at 6:26 am
This example is good for the purpose of changing the mindset of many developers -- continuing the laziness of not putting column names and only using * for everything. Which does slow down query time because the * requires a second lookup for all the column names before it can retrieve the data.
As a risk to security of hackers that like to "Select *", this could slow them down, because they would need to get the table columns in order to retrieve more info.
The paradigm of developers would have to change drastically for this to be implemented but I will suggest such an option to see how it is received.
It might not seem plausible right now but wait a while and some of us will make that leap to stop all the select statements when possible. Not if you can stop the tablename.* from happening, this might be received more favorably.
You have provided some valuable information and please do not stop because it was not as well received as it should have been.
Viewing 15 posts - 1 through 15 (of 140 total)
You must be logged in to reply to this topic. Login to reply