Devart Blog

Tidy SQL with dbForge SQL Complete

Posted by on August 1st, 2016

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 generally accepted coding standards with no efforts.

Increasing code readability

There are cases when SQL developers debug code or perform enhancement 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 mostly consistent in the way all scripts are written. Some have no coding standards and write code at 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 format tool.

My favorite tool to format messy code is dbForge SQL Complete — an excellent add-in 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 comparing 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.

messy-sql-before
Well, it is 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.

messy-sql-after

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 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 at your own will.

formattting_profile_new

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 and enhance its readability.

The Edit Formatting Profile wizard provides you with over 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.

Edit-FromattingProfile

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.

SQL-Formatting-Final-Result

When SQL code is well-formatted, you do not need to spend too many efforts to read it and analyze.

Adhering 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.

Summary

As you can see, dbForge SQL Complete is quite 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, SQL Refactoring that increase your productivity while developing SQL code.

 

Leave a Reply