This is just a rant. If you're going to give a tip, you should give reasons why your suggestion is worth following - the only reason you give is a single example of someone using Select * in a way that broke *your* code because they used it in a particular way. Select * can be used safely and there are many situations where it's use is valid - though I agree it's generally wasteful on bandwidth and the number of rows returned should be limited. And using it to transfer data between tables is a *BAD* idea.
Incidentally, it's also a *BAD* idea to alter the schema and code on a live site without validating the changes on a test system. Don't you have any test/QA procedures in place?
Yes, it's kind of implied that it is "select * where (some limiting condition)" to get the rows you want, or if you just want the schema you can use top 0 (but isn't there a proper way to get the schema with column types and everything for that?).
Reason for my vote of 3
John's later post indicates that SELECT * is the least of his problems. It sounds like everything about the project is a cluster thing. Chris's comment articulates it well.
Lashing out against SELECT * in frustration is understandable but unwarranted. We don't ban every SQL statement variation that might be misused.
This article is right but doesn't explain why... There are a few reasons, the main one being that you want your queries to be predictable:
- If a column is added, (poorly written) client code that uses column indexes may fail.
- If a column is added with a lot of data (say big documents) this may create a big performance impact.
- SQL may use different indexes (or no index at all) if it needs all columns since it may decide that e.g. an index seek + bookmark lookup is more work then doing a table scan.
Exceptions (since they return a predictable number of columns and are optimized properly by SQL):
- Using * in subqueries (such as SELECT A FROM (SELECT * FROM T)).
- Using * from a subquery (such as SELECT * FROM (SELECT A FROM T)).