SELECT * – SQL Smell

Is SELECT * FROM Table an SQL Smell?
Is SELECT * FROM Table an SQL Smell?

“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

SQL can be used: Interactively, in Batch files and in Programs
SQL can be used: Interactively, in Batch files and in Programs

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

SELECT * will continue to work even if columns are removed and added!
SELECT * will continue to work even if columns are removed and added!

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 *

Legitimate, safe uses of SELECT *
Legitimate, safe 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.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.