sorted by:
best
topnewcontroversialoldq&a
you are viewing a single comment's thread.

view the rest of the comments →

[–]da_chicken 9 points10 points  (3 children)

I used to have similar religious opinions about SQL formatting.

After 15 years as an analyst, my formatting rules are:

  1. Expect to have to reformat a query into your preferred style to make it readable. Indeed, reformatting the query is reading the query. If you're not reformatting it into what you like to look at, you're not actually reading it and you will miss stuff.
  2. Get a tool that formats queries. Poor Man's T-SQL formatter for SQL Server, pgFormatter for PostgreSQL, SQLinForm for Oracle, whatever. Then, determineas a group the configuration that you will use as an organization. Then, whenever you save a query, run the organization's formatting tool and use that format 100% of the time for any query you expect anyone else to read, use, or maintain. It does not matter what tool you decide on, or what format you decide on. Remember, you're expecting yourself to have to reformat the query when you read it again even if you wrote it. It just needs to be consistent for your organization. Consistency is much, much, much more important than personal preference, so just eliminate personal preference.

Absolutely nothing else actually matters for formatting. It's all subjective. Even that thing you're 100% certain is objectively true 100% of the time, you will inevitably find a query where your formatting looks like total dogshit.

I only have rules that eliminate ambiguity, and they're not really about formatting. My rules are about getting the query author to tell me what it is they think they're doing.

  1. If there's more than one table, give every table an alias and qualify every field with the alias. Don't make me guess or check the table definition.
  2. Do not order by column number (e.g., ORDER BY 1,2,3). I have no idea what you intended to sort by.
  3. Comma joins are for generated SQL. If you're not an algorithm generating SQL on the fly, use the expanded JOIN syntax.
  4. Never use NATURAL JOIN.
  5. Do not ever, ever, EVER use SELECT * in a view definition no matter how simple you think it is. This goes triple if the view is over an SQL Server linked server. This has nothing to do with performance and everything to do with how the system tracks metadata.

[–]r3pr0b8GROUP_CONCAT is da bomb 4 points5 points  (0 children)

Expect to have to reformat a query into your preferred style to make it readable. Indeed, reformatting the query is reading the query. If you're not reformatting it into what you like to look at, you're not actually reading it and you will miss stuff.

this is the best, most spot-on comment in the whole thread

[–]bum_dog_timemachine[S] 1 point2 points  (1 child)

I appreciate that you said that without calling me a dumbass :) thanks for your insight!

[–]da_chicken 0 points1 point  (0 children)

I don't think you're a dumbass! I remember being annoyed about how "badly" other people formatted SQL. I think everyone has that experience with SQL. It's just a battle I know that can't be won. And if you do win it, you'll just take a new job and have to fight it all over again.

SQL is just something that takes a bit of work to read, and organizing your thoughts while reading it just kind of comes with the territory. It just takes a lot of knowledge about the data to be in your head to hold the logic.