First, a list of things that are horrible:
- Leading commas
- All lowercase
- All uppercase
- Not putting new things on new lines (from join on where and or order by group by)
- Not indenting things
- Leaving AS out of table aliases
- Leaving out column aliases
- Top expressions without parentheses
- Not aligning parentheses
There are others, but I’m tired of thinking about things that are demonstrably wrong to do, like putting ice in scotch or sugar in wine.
Thank you for complying.
Not All Code
When you think about formatting code, you probably think primarily of organizing a single query so that it’s inoffensive to civilized society.
But you should also apply this to your code as a whole, too. People who use words wrong will call this “holistic”.
- Create all your temporary objects in the same section of code
- Declare all your variables in the same section of code
- Set as many of your variables as you can in the same block of code
- Do as much of your validation in the same section of code as you can
Few things (aside from the list at the beginning of the post) are more grating than seeing random things being created and declared at random points in the code.
It makes things far more difficult to scope, follow, and understand.
I’ll lend some leniency to using SELECT INTO here, because sometimes that’s the best way to get a fully parallel insert.
I’ll also lend some leniency to cursors, because sometimes it’s only sensible to declare them when they’ll be used.
Consistency Is Key
Formatting should follow a consistent set of (my) rules. It is sometimes possible to get used to inferior style choices as long as long as they’re predictable.
What’s impossible to get used to is code formatting that’s constantly changing gears.
There are many ways to format code out there in the world. Some are more acceptable than others.
For example, some companies may shy away from online code formatting tools because who knows who might see the code.
So much valuable intellectual property exists in your “joins” and “column names” and such. Someone else may steal the bright idea to also key on “identifiers”.
I personally use SQL Prompt, because it gets me about 80% of the way to how I want code to look.
While I do wish there were more ways to implement single formatting rules, like changing how columns are aliased from the shabby “column AS new_name” to the correct “new_name = column” format, it’s often trivial to tweak certain things after applying everything.
Thanks for reading!
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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.