Why is select bad

That pain hurts is a medical fact and backed up by literal TONs of literature and reference material on the neverous system. It is WAY beyond opinion. Bear in mind that this is by no means the biggest issue.

Breaking your application because a new column appears in the result-set would be far worse. Unfortunately the Database tables and code in question store information that is exchanged by differant middleware components for a limited time. The data stored in them is cleared out when the exchange is complete. The most rows that will ever be in this developers tables at any time is Yes I get it.

You must be logged in to reply to this topic. Login to reply. Post reply. November 14, at am If you use it in SSIS, for example, it can break your package if a new column is added. Same problem with anything implicit in code instead of explicit. Let me give you an example and you tell me. Help us help you. Fetch by name If the code is fetching column by name such as foo , and another table in the query adds a column foo , the way this is handled may cause problems when trying to get the right foo column.

Either way, a schema change can cause problems with the extraction of the data. Data overhead Some columns can have a significant amount of data associated with them. Lets look at some SQL fiddles that explore those schema changes a bit more. If instead your SQL statement was select one. Community Bot 1. Tonny I'd agree - but when I answered first I never thought this question would generate quite so much discussion and commentary!

Its obvious to query only for named columns, isn't it?! Breaking everything by adding a column is also a good reason why code should always access columns in a datareader by name not by hard-coded ordinal To them is may not be obvious.

Aaronaught I've updated it with the additional bit on the indexing issues. Wow, the accepted answer was so poor at actually explaining anything that I down-voted it. Amazed that this isn't the accepted answer. True story. Konrad Morawski Konrad Morawski 9, 4 4 gold badges 34 34 silver badges 58 58 bronze badges.

But you could still avoid and most people probably will it by querying with the wildcard and just add an alias for the identical column names. In theory, but if you use a wildcard for convenience you rely on it to automatically give you all columns in existence and never bother to update the query as the tables grow. If you are specifying each and every column, you are forced to go to the query to add another one to your SELECT clause and this is when you hopefully spot the name is not unique.

BTW I don't think it's so rare in systems with large databases. And I found another case just now: stackoverflow. I spend an hour last week trying to get this through a a consultants head. He is supposed to be a SQL guru Add a comment.

Querying every column might be perfectly legitimate, in many cases. Always querying every column isn't. Phill W. I would think that always querying every column may be legitimate for things like schema-agnostic table viewing facilities. Not a terribly common situation, but in the context of internal-use-only tools such things can be handy. Usually in that case it's more about "what columns are there and some sample data" than the complete content. Tonny: SQL Server changed their default scripts to add the TOP limitation; I'm not sure how important that is if code reads as many as it cares to display and then disposes the query.

I think query responses are processed somewhat lazily, though I don't know the details. In any case, I think that rather than saying it "isn't legitimate", it would be better to say " And I really like the way you put it in your last sentence.

I have to remember that one. Learn SQL! It isn't that hard. It is the "native" language of databases far and wide.

It's powerful. It's elegant. It has stood the test of time. And there's no way you're going to write a join on the "code" side that's more efficient than the join in the database, unless you are really inept at doing SQL joins. Consider that in order to do a "code join," you have to pull all the data from both tables in even a simple 2-table join. My instincts tell me that the latter would win, for several reasons This gives us a ten thousand row table, with one very large column to add a substantial amount of bloat.

To turn it on, you need to click this little button on your SSMS Scan count 1, logical reads , physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. As expected, the number of page reads are identical, but the CPU and elapsed time are miles different!

The first query, returning some eighty million more bytes adds seven seconds onto our query time! Again, you can see the difference in the amount of data sent to the client, and the massive increase in client processing time that this causes.

Please read this article. These are just the two top issues from database perspective. Remember that the client needs to process the data too—which might put a considerable load on garbage collection. Now that we have established a common understanding of why selecting everything is bad for performance, you may ask why it is listed as a myth?

Further they believe they are not committing this crime because their ORM lists all columns by name anyway. In fact, the crime is to select all columns without thinking about it—and most ORMs readily commit this crime on behalf of their users. This is the bad thing. But if you need a more catch phrase to remember the truth behind this myth, take this:. Besides the performance issues mentioned above that are not caused by the star asterisk itself, the star itself might still cause other trouble.

The focus of the article is on very subtle issues which are hardly understood, hard to find, and often even hard to fix e. The main goal of this article is to stop people thinking about the star itself. Once people start to name the wanted columns explicitly to gain the performance benefit explained above, the issues caused by the star itself are also gone. He is on a mission to introduce developers to the evolution of SQL in the 21st century.