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)).
Select * is okay when you really do want the whole table schema, typically for taking a temporary copy of some rows for a complex update operation or for displaying it to the user. In business logic it is almost always a mistake, and if you are making assumptions about what it returns it can be a dangerous one.
Select * isn't okay if you only want the whole table schema.
Back in the days when I was still new at [the company] and doing data analyst (with a little background in programming) work, one of the application keeps getting slower and slower by the week. Every time we loaded some data into SQL, it would take longer to run the app - from 1 minute to 5 minutes then 10 minutes... I was so frustrated and started to dig into the code and found the culprit - "Select *".
It turned out, one of our programmers wanted to get the schema of the table and used Select * to retrieve it. That is okay if the table is small but if your table is growing constantly then this will be a huge problem.
After discovering the problem, I changed the code to Select Top 0 * and the problem was gone and the app ran faster than ever. So if you really want to get the schema of the table and do not know it before hand and need to used Select *, make sure you limit the number of rows returned.
Just to be Devil's advocate here - well, not quite - doing a Select Column1,..., ColumnN from Table can be just as dangerous. Was the issue because you had two tables both with a column name with the same name? Select Table.Column1,..., Table.ColumnN from Table will at least allow the stored proc to compile, but even then your rowset will contain duplicate column names.
Correct database change management is what will save you here. Select * is not the problem, it's a symptom.
We didn't have the time to figure out exactly why the queries went nuts. We have until 08/08 to get the changes I'm working on implemented, tested and deployed. All I know is that I added some new columns to an existing table, and another part of the site (AND an associated desktop app) got buggered up as a result. Removing the columns brought the universe back into alignment. None of MY queries use SELECT *.
Regarding change mamangement, that would be great if we had an actual DBA on the team. Essentially, there is NO management regarding the source code, and it even appears that much of it isn't even in source control. The only documentation associated with any of the projects we're working on is the documentation I'VE written, and I would venture to guess that fully 99% of the code I didn't write has absolutely NO comments. What we need is three more programmers and a full-time DBA on the team to completely rwrite everything from scratch and get rid of the external processes (like DTS scripts and batch files that move data from a hodgepodge of disparate Access databases into SQL server. The problem with that plan is that we work for a defense contractor, so not only are we not getting anyone new, one of the seats on our contract is going to be deleted, meaning one of the four existing programmers will no longer be with us by next June. Since I'm the new guy, it's highly probable it will be me.
Reason for my vote of 5
Agree, causes much confusion and extra maintenance. I think the only situation where * is beneficial is when you have no other means to figure out the table's structure (columns, data types etc)
Last Visit: 31-Dec-99 18:00 Last Update: 31-Jul-14 19:29