How to prevent SELECT * statements

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan – you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

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:
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:
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!
Like or share to get the source code.

-Klaus
Advertisements

Author: Protiguous

C# Software Developer, Father, and seeker of Truth.

Leave a Reply

Fill in your details below or click an icon to log in:

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