Formatting TSQL code

How do I format my TSQL code?

Everybody has it’s own preferences on formatting SQL or TSQL code. I follow few very simple rules to achieve better readability, less bugs and  – you wont’ believe – faster code execution! Code that is not using two-part names will compile a separate execution plan for each user. Multiple exec plans take more memory than it would when using two-part names. More compiles means more wasted CPU, more latches and compile locks. Latches and locks are serialization mechanisms. They make things wait, and execute one-by-one (not in parallel), making your 100 core monster perform like a poor single core machine.

Can you spot the bug in this code?

In the inner SELECT from dbo.ufnGetContactInformation, the LoginId is not from that function, but a field from HumanResources.Employee table. The code executes without reporting an error, but gives unexpected results. This bug would be reported in compile time if we have used aliases, saving us ton of trouble of debugging it later or in production:

Msg 207, Level 16, State 1, Line 3
Invalid column name ‘LoginId’.

Aliases should be used every time, no exceptions. Even if you have only one table. There are benefits of more robust code, as we saw in previous example.

Here is the short video with all my formatting tips

Interesting thing is, there is currently no software that I am aware of, that can automatically format the code like that! If you find one, please let me know.

Question for you: I made one formatting error in this video example. Can you spot where it is?

It has been a pleasure writing and recording for you. Please press “like” if you find this useful.

Until next time!

Appendix: Tools for formatting SQL

So far I haven’t found any tool that can format exactly like this. It seems “impossible” to align parentheses vertical and indent blocks by 1 TAB from parent. So simple, so natural, but NOONE implemented it so far!??? Sad but true. Some come close, but lack “1 TAB indent”. They end up indenting super-far right (eg. 200 spaces), instead of 1 NEW LINE + 1 TAB. Still, they are not completely useless, can do a part of formatting and you can continue manually from there. UPDATE: SQL Prompt CAN format like this! But there is no template provided, you need to configure it manually, plus it is not free.

• RedGate online SQL Formatter – free, handy, but CANNOT format as described.
• ApexSQL Refactor (free)
  Warmly recommended, can format beautifully, is reliable, and is free! The only thing missing is “condensed” formatting: to put first item of the list in the same line, and not breaking multiple items if they fit into one line. But not a big issue, condensing is not a “must” it would just save few lines of vertical space.
  Here are formatting settings already prepared for you. Import them into ApexSQL Refactor’s Options window. Built with version 2018.04.0337: Download Formatting Settings
• Online “light” version of ApexSQL format is here! I change these options:
  • Formatting -> check “Indent using tabs”. “Tabs equal 3 spaces”.
  • Formatting -> Miscellaneous -> check “Remove unnecessary brackets”
  • Statements -> check “Don`t format if parentheses are not nested” <- a huge difference, but still not indented.
  • Lists -> Columns -> Format -> check “Place list on a new line” and “Indent 3 spaces”
  • Joins -> Format -> Place JOIN keyword in new line -> check “Align with the beginning of previous keyword” and “Indent 3 spaces”
• sql-format.com
  Maybe even closer, not free, but online version IS free! I change these options: 
  • Keep tabs, size 3
  • SELECT -> FROM -> JOINs -> clear “Line break before ON“
  • Subquery -> check only “Line break before open brace”, “Line break before closing brace”, “Indent subquery”
• Redgate SQL Prompt beta version 7.3.0.639 released today (I had honor to help them a bit with this release) is the FIRST tool that can format exactly this style! So far, it is the only tool I know of that can do it. And it is almost a default. There are only 2 differences that I had to customize from default style: TABS only (instead of spaces), and “ON” in the same line as JOIN. Finally – what a joy! Good job for the RedGate Team!
• SQLinForm online tool is very close to desired formatting, with settings like this:

The only downside I noticed is it can’t have “ON” condition in the same line with JOIN, and it can’t use TABs (it uses spaces), but those are minor problems. It is free and also has an app (free variant is limited to 100 lines) and a Notepad++ plugin (free variant has limited set of options) that does formatting similar to online variant.