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:
-- 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:
-- 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:
-- 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!
-Klaus
12 thoughts on “How to prevent SELECT * statements”
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.
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.
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!
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. 😉
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.
Hello Horia,
As soon as you add an ORM to your project, the project is anyway failed 😉
-Klaus