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 (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:

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

12 Comments

  • Jeff Moden

    It is clever but, to be honest, I’d fire anyone that would do such a thing. There are times when a DBA has to make some very quick decisions during a crisis and SELECT TOP X * is sometimes necessary in troubleshooting, especially on wider tables.

    • This is nice approach in UAT/QA environment instead of Production environment. It will also avoid the scenario that need urgent troubleshooting in Production environment.

  • Toby Harman

    While I love the idea of this, I’d have to agree with Jeff. Most of the times we see “SELECT * FROM table” it’s the DBAs chasing some issue and wanting to get answers as fast as possible without having to learn the entire schema.

    Code practices like this should be caught in code review. Automated code review tools looking for “SELECT” and “*” with fewer than 5 characters in between will throw this up on the developer side.

  • Fact of the matter is that if I create a view which includes a “SELECT * FROM” and then I change the table schema, I need to RE CREATE the VIEW for it to be usable again.
    And now days, when yous want to “SELECT *”, write clicking a table name would give you the column names
    So, unless it’s a quick and dirty check, “SELECT *” are not as common as they used to be.

    • Regarding “if I create a view which includes a “SELECT * FROM” and then I change the table schema, I need to RE CREATE the VIEW for it to be usable again.”:

      No, you definitely do not need to drop and re-create a View if there is a change to any of the underlying columns. All you need to do is execute:

      EXEC sp_refreshview N’SchemaName.ViewName’;

  • It’s a fun trick. But I simply don’t care enough about it to forbid it and I’d never block it.

    99% of the time you’re just making more work for others running queries and tediously filling out column lists and then updating them later. But when a query kills a server the problem likely isn’t select * but rather that nothing was thought out in the first place.

    DBAs who create ways to make life harder have way too much time on their hands in my humble opinion. As a DBA I’m flat out keeping things running and trying to add value to the business to make people happy.

    Not this stuff.

  • if
    SET ARITHABORT OFF
    SET ANSI_WARNINGS OFF

    division by zero “fails” and select * is executed with only warning triggered.

  • Markus

    I love this idea and would really really like to apply it to all out tables hundreads of * queries are executed against and slow down performance. Unfortunately I will get in more trouble as I actually am if i did…
    However, thanks for the tip, I will consider this in future (new) solutions!

  • Alan Speckman

    I use redgate SQL Search to find select * on databases I inherit. I do an exact match. Funny thing, I have DLM Dashboard running and found a select * from the DDL_Events table on the ReadEvents sproc. 😉

  • Marrow Yung

    Alan,

    do you mean the internal SQL server DDL DMV/SP also doing select * too ?

  • The moment you add this table to an ORM, your app will fail (at least if you use EF6, LLBLGEN). I`m not a fan of ORMs, or mapping a table directly .. rather use stored procs and views instead, but there are billions projects out there who do map tables directly.

    • Klaus Aschenbrenner

      Hello Horia,

      As soon as you add an ORM to your project, the project is anyway failed 😉

      -Klaus

It`s your turn

Your email address will not be published. Required fields are marked *

SQLpassion

Copyright © 2015 by SQLpassion · Klaus Aschenbrenner · Imprint · Offerings · Academy · Contact · Go to Top