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!

UPDATE:

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): http://sql-format.com

UPDATE2 26.10.2016:

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!

Posted in TSQL and SQL Client Code
6 comments on “Formatting TSQL code
  1. Vedran Vedran says:

    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. 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 🙂
    Cheers,
    Jan

  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. SSMSboost.com

  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.

    Cheers,

    Erik

Leave a Reply

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

*