Introduction
During my SQL Server classes, I'm asked many times how to format T-SQL. If you do a Google search on How to Format T-SQL, you will get a ton of results. There are many great posts on this topic. This post will identify some industry standards for formatting and my personal thoughts on formatting.
As you may know, formatting is very important. If we have a production outage that is caused by a stored procedure, as the DBA you might be asked to review the code in the stored proc. This in itself is expected, however, what might not be expected is poorly formatted code. This poorly formatted code could actually lead to a longer outage because now you will need to take more time to read the code to figure out what it is doing.
This is true especially if you didn't write the code, or you did write the code but don't remember it because it was a long time ago.
Capitalize all Keywords
While this in no way is a requirement, I believe that by doing so the code is cleaner and easier to read. I also think the keywords jump out more when they are in caps.
- select *
- from production.Product
- where ProductID > 350
With keywords in Caps:
- SELECT *
- FROM production.Product
- WHERE ProductID > 350
Alias All Tables
I like to alias all tables, even if the query only uses only one table. The reason for using an alias even with one table is that if that query evolves into a query with more than one table, that initial table already has an alias and is set up for the addition of more tables.
When I use a table alias I have two simple rules I follow.
- It needs to be somewhat descriptive - The reason for this is pretty straight forward. I feel it makes it easier to determine which table all the columns are coming from.
- All aliases should have the same number of characters - When I write T_SQL code, I find it easier to read if the dots between the alias and the column name. If the alias is the same length this is easier to do.
The code below has three table aliases, all of different lengths. To me, it just seems busier and more difficult to read.
Alias All Columns
When there is a table alias, we should be using it for all columns. When a column exists in both tables in the join, you will get an error similar to below.
Msg 209, Level 16, State 1, Line 2
Ambiguous column name 'ProductID'.
Using an alias on all columns, you can prevent this error. You can also make it easier to figure out what table each column comes from. Using the example below, it can be a bit challenging to figure out which table the ListPrice column comes from. When looking at the tables involved in the query, it could logically come from two tables, Production.Product and Sales.SalesOrderDetail. Because of the lack of an alias, this task becomes more difficult.
Use Vertical Lines
I like to line up the elements of the statements. If you look below, you will see an example of what my code will typically look like.
By doing this, for me, it is easier to identify which SELECT, FROM and WHERE are part of the same statement. As well as which ON goes with which JOIN. I also feel that by doing this, the code is cleaner and easier to read.
Place Column Names Vertically
Over the years, I have had to review code that was written by others, and honestly sometimes myself, with the columns placed horizontally, similar to the first image below. What I have found is that by placing the columns in this manner, it becomes more difficult to review the code. Especially if there is a function or a CASE statement.
By doing this, it will be easier to add or remove columns from the result set.
Place the Comma before the Column Name
If you can place the comma before, of course you can also place it after the column name. While some believe placing it after is the way to go, I have found that placing it before the column name works better for me. While you may develop your own preference, I think the most important thing here is that you have a standard and be consistent following it.
Looking at the example below, you can see that by having the commas at the front it is a bit easier to comment out a column. The only exception would be the first column. If you comment out the first column, there is still the comma at the front of the second line that will cause an error.
Steps in the FROM and WHERE
When working in the FROM or WHERE clauses I like to format the code in a way that resembles steps. I like to line up the INNER or OUTER keywords in the join, but on the ON keyword.
By doing this, I have found it easier to pair up the ON and the JOINs.
I also like to do something similar in the WHERE clause. By placing the AND keywords on different lines and offsetting them, again similar to stairs. I think it is easier to read what the criteria for the query is.
Comments
Comments are critical to any well-written code. T-SQL must be self-documented and comments is how this can be done.
There are two methods you can use to comment your code. The first is by using two dashes. This will comment out any code that is to the right of the two dashes. In the image below, there are two examples of comments using the dashes.
The second method is to use a set of characters, /* and */. Any text between these will be commented out, as you can see below.
Tools to help format
PoorSQL Formatter
This is an extension for Azure Data Studio. I really like this extension and it is very easy to use. In order to utilize this, you will need Azure Data Studio and the extension is self. Here is a link to my blog
post on the topic.
Websites for formatting
These are just a few sites I found online. Please review them carefully and use them at your own risk.
- http://www.dpriver.com/pp/sqlformat.htm
- https://sqlformat.org/
- https://codebeautify.org/sqlformatter
While following industry standards is important, it is also important just to have a standard. Hopefully, the standard you follow will have it's roots in industry standards.
These are in no way an all-inclusive list. There are other guidelines as well. These are just the basic ones I have followed for a number of years.
Again these are just my thoughts and have worked for me for quite a few years.
Thanks for visiting my blog!!!