An article written by Andrey Langovoy
In this article, we will talk about how to keep SQL code clean and highly readable. We will also discuss how to stop suffering from messy and unformatted code. In addition, we will talk about how to force all team members to adhere to generally accepted coding standards with no effort.
Increasing Code Readability
There are cases when SQL developers debug code or perform enhancements on existing or legacy code. For instance, while initiating a code review process, developers deal with different coding styles and techniques. Some have coding standards and are mostly consistent in the way all scripts are written. Some have no coding standards and write code with their own sweet will. This requires a reviewer scrolling horizontally a few times to read the code entirely.
It is much easier to understand the flow of the code when it is well-formatted and has a good degree of consistency.
When the code looks unreadable, I would prefer to use some sort of SQL formatting tool.
My favorite tool to format messy code is dbForge SQL Complete — an excellent add-on for Microsoft Visual Studio and SSMS. Along with automatic code formatting features, SQL Complete provides outstanding IntelliSense-like functionality, which makes your work much more productive compared to manual coding.
Let’s have a look at how the tool formats code.
The following image demonstrates the real (taken from the production) example of the unformatted SQL code. Besides the formatting problem, you might notice that the keywords appear in the various case across the text.
Well, it's not so easy to understand what the code does.
Thankfully, SQL Complete gives us the ability to format messy SQL code by using a well-known shortcut. Let’s press CTRL+K+D and look at the output.
Managing Code Formatting
Now it’s looking much better, isn’t it? The next step is to decide whether the standard formatting output meets your requirements. If not, you can easily adjust it by tweaking formatting options. To do so, go to the SQL Complete menu and click Options. Navigate to Formatting Profiles.
There are three predefined formatting profiles that provide you with various formatting options. Try to select various profiles and see the result.
In case it doesn’t work for you, it’s easy to create a new profile and customize it.
Once you’ve created the new profile, you can select it and edit.
Let’s customize the new formatting profile to add more structure to the existing piece of SQL code to enhance its readability.
The Edit Formatting Profile wizard provides you with more than a hundred formatting options that cover any needs. You can use the search functionality to find a required setting in the tree. Additionally, you have a quick preview pane that adds more convenience to the process.
First of all, let’s place each statement on a separate line, add the line break after WHERE, and indent conditions slightly.
Let’s uncheck the Place comma before the item in stacked list option. For the subquery, clear the "use same formatting as in SELECT" checkbox, and select Line break after opening brace. Also, add line breaks after WHERE and FROM.
Once you finish the tweaking process, format the code again to assess the result.
When SQL code is well-formatted, you do not need to spend too much effort reading and analyzing it.
Adhering to Coding Standards
The formatting profile is nothing but a simple XML file. You can share the file within your team. This way, each SQL developer will have an ability to commit SQL code to the project that is formatted according to accepted rules.
As you can see, dbForge SQL Complete is a simple and intuitive tool that provides advanced code formatting features. Also, the tool provides you with a number of features like Code Snippets, Automatic Code Completion, and SQL Refactoring that increase your productivity while developing SQL code.