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.
FirstNameandLastNamewas being reversed. I missed my flight because someone wroteSELECT *instead ofSELECT FirstName, LastNameand 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 ofSELECT*. 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.
I won’t argue with you over select 1 vs select *, I can see that point of view, especially if using tools (or queries) that can’t tell the difference for sure.
The TOP advice is great advice, and probably something g more tools could to for you too. DBeaver does that in the client which is great for ad hoc querying (but it does do it silently so if you need more rows and forget it can be annoying).
Really glad that SSMS is really good at killi g a query these days (and not crashing when it runs out of memory when you do return too many rows).
As always, love your site!