Software Vendor Mistakes With SQL Server: Not Enforcing Code Formatting

On Top Of Spaghetti

I’m going to start sending dead fish to anyone who tells me that a Common Table Expressions make queries more readable. If your code is formatted like crap, they don’t do anything.

Good formatting is the basis for code consistency. If things are a sloppy mess, no one will ever want to fix them.

In this post, I’m going to cover two things:

  • What you should always do to make code easy to understand
  • What you should consider doing to format your code so it’s easy to understand

The first is fact, the second is opinion. Laziness is not an argument in opposition to either one.

Let’s boogie.

SQL Formatting Facts

Here are a list of things that make your code look good:

  • Capitalize things consistently: SELECT, select, SeLeCt — pick one and stick to it
  • Schema prefix all of your objects, even if everything is in dbo
  • Alias columns consistently, and…
  • Give your tables meaningful aliases, not just a, b, c, d
  • Embrace new lines for things like FROM, JOIN, ON, WHERE, etc.
  • Indent things appropriately so logically connected blocks are easier to spot
  • Ending your queries with a semicolon (;) makes it easier to figure out when a query actually ends

Sure, there’s probably more for specific things like stored procedure, where I’d say you should handle all your variable declarations and table creations in the same blocks of code, but whatever.

SQL Formatting Preferences

Here are a list of things that make your code look good to me:

  • Putting commas at the end of column names
  • Indenting columns four spaces in from select
  • Using alias = expression rather than expression AS alias
  • Consistently upper casing key words
  • Not upper casing data types
  • Using TOP (n) rather than TOP n
  • Putting the ON clause of joins on a new line

There’s more, and some of proper formatting is situational. For example, I like to really use new lines and indenting for complicated expressions with nested functions to make the inputs clear, but for short ones I usually won’t spread things out.

All this has developed over years of writing and reading code to learn what works best for me, and what I think looks right. I don’t expect everyone to agree on every point, of course, but things like old-style joins and ordering by ordinal positions just looks bad.

I have a long-standing disagreement with a dear friend about table aliases being capitalized. I don’t think they should be; he thinks they should.

Despite that, I can still read his code just fine.

Not A Nit Pick

I have these opinions because I write and review a lot of T-SQL. The harder code is to read, the harder it is to tune, rewrite, or spot nonsense.

These days, there’s almost no excuse for it, either. There are a half-dozen free and paid T-SQL formatters that work with a variety of IDEs.

I don’t have a specific recommendation here, because I haven’t been able to find a single tool that gets things right. Most get you 80% of the way there, and the rest is up to you.

What I’d really like is a tool that could also format and make suggestions in dynamic SQL, but I can’t imagine there’s enough money in that to warrant the effort.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

10 thoughts on “Software Vendor Mistakes With SQL Server: Not Enforcing Code Formatting

  1. Formatting SQL Server queries, stored procedures, everything – IMO you cannot beat Redgate’s SQL Prompt. It is fast and consistent. Then you can use Redgate’s SQL Compare to see the differences in different versions.

    It prefixes all objects and adds semicolons and has many other useful functions.

    Con: $179 a year. If you live and breath in SSMS it is worth it.

    No, I don’t work for Redgate.

  2. This has been an amazing series so far. How do you get SQL Prompt for 75 a year?

    If it’s a secret, I’ll never tell.

  3. Most of everything here is something I agree with, though I would like to ask about this: “Using alias = expression rather than expression AS alias”

    Any particular reason? The only thing i could say counter to that is that it puts your columns names out of sync (like having one column name left adjusted as opposed to right adjusted) with everything else, but I am also willing to change my perspective as well.

      1. IDK why i thought it would be out of sync. Like its either the column name or its not, lol *facepalm* I do agree that indenting away does improve readability so I guess I have a new habit to adopt, thanks.

  4. Something I would add is using SET vs SELECT for variable assignments.

    I prefer SET for various data safety reasons, though I see SELECT around since it’s easier (and cleaner? 👀) to assign multiple variables at once with that way.

    Kind of a formatting issue, kind of not.

Comments are closed.