Categories
Anything

How to prevent SELECT * statements

Interesting idea! October 26, 2015 · Klaus Aschenbrenner · Leave a Comment

Everyone of us knows that it is a bad idea, but we still do it sometimes: we execute SELECT * statements. There are so many different drawbacks to that approach:

  • You return every column single from your table, even columns that are added at a later stage. Imagine what would happen in your query if a VARCHAR(MAX) were to be added in the future…
  • You can’t define a Covering Non-Clustered Index for the specific query to overcome an unnecessary lookup operator in the execution plan, because you would duplicate your table data in the additional index…

The question is now how can you prevent SELECT * statements? Of course you can perform code reviews, you can provide best pattern guidance, but who on earth pays attention to these things? Almost nobody – that’s unfortunately the sad truth… But there is a very simple way to prevent SELECT * statements on the technical level within your table.

A few weeks ago I have attended the SQLSaturday in Holland, and Aaron Bertrand (BlogTwitter) presented a session about T-SQL Bad Habits. And he also talked about SELECT * statements, and how to prevent them. The solution to this problem is quite simple: you add a computed column to your table definition that generates a divide by zero exception. That approach is amazingly simple, but really effective. Let’s have a look at the following table definition:    

CREATE TABLE Foo(
 Col1 INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
 Col2 CHAR(100) NOT NULL,
 Col3 CHAR(100) NOT NULL,
 DevelopersPain AS (1 / 0)
);

As you can see I have added here a computed column that performs a division by zero. This means that you will get an error message when you select this column – like in a SELECT * statement.  

The SELECT * statement doesn't work anymore!

But on the other hand when you explicitly reference your columns by name, you don’t return the computed column and your query works as expected:    

SELECT Col1, Col2, Col3 FROM Foo;
Referencing the column explicitely works...

Nice, huh?

Summary

As I say very often in my various workshops: sometimes we just get too complicated! The approach with the computed column is quite simple – but of course it will need a table schema change. But think back to that approach the next time you start with a fresh new table design. Aaron, thanks for this great idea!

-Klaus

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s