Thoughts on SELECT *

Louis Davidson defends a slighted bit of syntax:

I was about to walk out the door to take a flight, when my phone rang. Our major software system we had just released last week was returning weird data. FirstName and LastName was being reversed. I missed my flight because someone wrote SELECT * instead of SELECT FirstName, LastName and a table structure was reorganized.

Ok, the story was a fabrication, but I wanted to start out with a story that could resonate with the reader. In this post, I want to say a few things about the use of SELECT * and I wanted to make sure it was clear that I am not encouraging more use of SELECT *. Not at all.

I agree with Louis that SELECT * is fine for ad hoc querying. It’s not so great for application code because of the story Louis tells above, but if you’re just checking the contents of a table, whatever. The habit I’d much rather drill into somebody’s head is always have TOP in an ad hoc query. Condition yourself to write SELECT TOP(100) before you start a query. Or have your auto-completion tool of choice (e.g., SQL Prompt) do it for you.

As far as SELECT * or SELECT 1 in EXISTS clauses goes, I used to be in Louis’s camp, though I had a former manager who explained her preference for the latter: if you standardize on SELECT 1 in those clauses, it makes it a lot easier to find inappropriate uses of SELECT * in application code.