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.

8 Comments on “Formatting TSQL code

  1. The query example I worked on was taken from MSDN examples here, paragraph E: https://msdn.microsoft.com/en-us/library/ms187731.aspx
    Here it is so you can practice on it, do your own formatting, or if you are a manager or team leader, you can make that a task for your DB developers:

    SELECT DISTINCT pp.LastName, pp.FirstName
    FROM Person.Person pp JOIN HumanResources.Employee e
    ON e.BusinessEntityID = pp.BusinessEntityID WHERE pp.BusinessEntityID IN
    (SELECT SalesPersonID
    FROM Sales.SalesOrderHeader
    WHERE SalesOrderID IN
    (SELECT SalesOrderID
    FROM Sales.SalesOrderDetail
    WHERE ProductID IN
    (SELECT ProductID
    FROM Production.Product p
    WHERE ProductNumber = ‘BK-M68B-42’)))

  2. Hi Vedran,

    you left there this:
    SELECT d.SalesOrderID
    FROM Sales.SalesOrderDetail d
    WHERE ProductID IN

    While you should specify WHERE d.ProductID IN …

    Nice formatting lesson there 🙂
    Cheers,
    Jan

    • There is a SQL formatting tool that does also a lot of other cool things. It’s free and is called SSMSboost. SSMSboost.com

  3. Hi Vedran,

    I’m using DB-Forge from Devart and this tool has pretty sophisticated formatting options for custom formatting profiles, though never had time to explore, if I can bend it as you showed in a video.

    Cheers,

    Erik

    • Nice, but it amazes me that almost NO TOOL (except redgate) can do the simplest formatting of all:
      – if (sub)statement fits in single line, keep single line. Else:
      – keep closed parentheses VERTICALLY ALIGNED with matching open parentheses, indent the content (block)
      – Keep DML keywords (SELECT, FROM, WHERE, GROUP BY) vertically aligned, and content indented. Same as they are block start which ends with next keyword.

      That should be default imho, but seems as impossible taks for almost all formatters, even most advanced ones. Despite tons of features, vertical aligning of parentheses looks unreachable for them, at least so far.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.