“SELECT *” What could possibly be wrong with that? Everybody uses SELECT *, don’t they?
Phil Factor describes this as an SQL Smell, and it is. Finding it should immediately make you suspicious. The problem is not the statement, it’s where you find it!
Interactive and Batch SQL
You can use SQL interactively, in “batch”, and inside programs. One of the good things about SQL is that it looks pretty much the same wherever you find it.
“SELECT *” is intended to be used interactively. That’s how I use it, and I expect Phil Factor does the same. Typing the statement in the figure at a command line, or inside a development environment like SSMS is completely appropriate.
Some people create queries interactively using “SELECT *” as a starting point. That’s legitimate too. It’s a matter of personal style.
Don’t use this form of SELECT in a program or when you expect to reuse it. If you save the file, you shouldn’t be using “SELECT *”.
Why Using “SELECT *” is a problem
Sometimes we want things to break! We want something to fail before something worse happens.
You can change the design of tables in a database. One way is using the ALTER statement. Columns can be added and removed.
“SELECT *” will continue to return a result even when the tables it is using have changed significantly. This is a problem because we don’t know if it is still doing what we originally intended!
Legitimate uses of SELECT *
There are a few ways you can use an asterisk in a SELECT statement without taking a risk. That is when you are checking if something exists, or counting the number of rows. In both cases the columns of the tables are irrelevant.
Summary
Phil Factor identifies “SELECT *” as an SQL Smell. It can be used interactively, but almost anywhere else it has the potential to cause problems.
Where next?
Do you use “LIKE” in searches? There times when Phil Factor thinks this is an SQL Smell too. Find out why in the next article.