Preventing usage of "SELECT *..."

  • Comments posted to this topic are about the item Preventing usage of "SELECT *..."

    The vision must be followed by the venture. It is not enough to stare up the steps - we must step up the stairs. - Vance Havner
  • Interesting but i feel its not practical to add dummy columns to tables. 🙂

    "Keep Trying"

  • 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.

  • 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

    www.garyhowlett.co.uk

  • Indeed a good article but practically difficult to use.

    -Forum Etiquette: How to post Performance Problems[/url]

    -Forum Etiquette: How to post data/code to get the best help [/url]

  • 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

  • I think you can still use 'select anyfield, tblname.* from tblname' though.

  • 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.

  • May not be practical.

    May not be standard.

    But it is a good/smart solution.

    🙂

    Regards.




    Fernando Ponte
    factdata.com.br

  • But it does not solve the problem when I can use

    select tblname.* from tblname

    with the same result.

  • 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 ?

  • 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.

  • 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!

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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