SQL Issue: SELECT * FROM Table
While troubleshooting an application, I stumbled across this SQL query issued by the app.
SELECT *
FROM ServerLogs
Most developers that interact with SQL have been taught to not use "SELECT *". But for inexperienced developers, this is often a trap they fall into. It's easy to write "*" and it always returns a superset of what you need. So it's confusing when there's a 'rule' to not use "SELECT *".
At a high level, here are a few of the reasons it's considered bad practice.
- SQL is very good at optimizing queries. When you use "SELECT *", you restrict its available options. The SQL engine decides which indexes to use, when and how to perform queries, and what to push into memory. For the most part, because "SELECT *" is requesting all the columns, SQL will eventually have to read all the columns and thus has limited options.
- Unless you're using all of the columns, you're transmitting more data than required over the network. It turns out, networking bandwidth can still be a bottleneck for enterprise systems. If you only request the columns you require, you're helping everyone that queries SQL.
- "SELECT *" isn't explicit about what order to return the columns. So even if you really needed all the columns, you may receive them in a different order if the table is rebuilt. As a side note, from an application's perspective, any schema change is bad. If the columns are reordered, removed or added, it's all unexpected to the application.
If possible, avoid using "SELECT *". It's not the worst inefficiency in your code base, but it's sneaky. Apps always work on Day 1. But subtle bugs like "SELECT *" are the ones that bite you months later.
To hear the speech version of this post click the play image.
Brought to you by @tts. If you find it useful please consider upvote this reply.