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. Therefore taking 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 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 error, giving you unexpected results. This bug would be reported in compile time if we have used aliases, saving us 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 with 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 deliberately made one formatting error in the video example. Can you spot where it is?

It has been pleasure writing and recording for you. Please press “like” if you liked it.

Until next time!


In the meantime, I found two tools that can go quite close to this formatting, but not exactly the same: ApexSQL Refactor (free) and maybe even closer (but not free):

UPDATE 26.10.2016

Redgate SQL Prompt beta version 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!

UPDATE 9.11.2017

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.

6 comments on “Formatting TSQL code
  1. Vedran Vedran says:

    The query example I worked on was taken from MSDN examples here, paragraph E:
    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. Jan says:

    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 🙂

  3. Vedran Vedran says:

    Thanks Jan. You are right, “d.” is missing before ProductID.

    • Goran says:

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

  4. Erik says:

    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.



Leave a Reply

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