How to prevent SELECT * statements

Interesting idea!

October 26, 2015 · Klaus Aschenbrenner · Leave a Comment

(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 (Blog, Twitter) 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:

1 2 3 4 5 6 7 8 9 10

-- Create a simple table with a computed column that generates -- a divide by zero exception. 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) ) GO

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:

1 2 3

-- A SELECT * statement doesn't work anymore, ouch... SELECT * FROM Foo GO

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:

1 2 3

-- This SQL statement works SELECT Col1, Col2, Col3 FROM Foo GO

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