Free SQL formatter overview

In this guide, we’re going to discuss the core concepts and theory around T-SQL coding practices and the role of the free SQL formatter tools.

The computer code always describes the intent of the software.  When it comes to the database it’s no different. When T-SQL is read, the developers/DBAs should understand what, why, and how the T-SQL functions. Needless to say, it’s a daunting task. Everyone and everything involved in the team or project must understand each other. The fact is that things always tend to change and mistakes happen.

In the latter part of the article, we’ll see the 3rd party free SQL formatter tool—ApexSQL Refactor and the basic guidelines and tell you the importance of the coding standards that you find the style and stick with it consistently. As the SQL gets more complex, it becomes more difficult to read and indentation and formatting become much more important.

SQL Formatter elements

The basic understanding of the SQL formatter elements helps to define SQL coding standards.

  1. Identifiers
  2. Data Types
  3. Constants
  4. Functions
  5. Expressions
  6. Operators in Expressions
  7. Comments
  8. Reserved Keywords

The basic guidelines of a SQL formatter

There are a number of common SQL formatter programming style conventions can be defined. Let us list few of them and then go into the detail about each.

  1. The first is around comments, which programmers read and interpreters ignore.
  2. Next is whitespace or unused space around an object.
  3. Different naming conventions for identifiers such as variables, functions, and methods are included.
  4. Reserved keywords must be used only by SQL Server engine and shouldn’t be used as names of objects in the database
  5. The use of constants and expressions, as expression, include functions, constants and return a single value
  6. The conventions also have a way to avoid possible human errors such as unreachable code, there are a number of other conventions as well that don’t fit any of these subjects such as security, spelling and more.

Let’s take a closer look at SQL query formatter options. The SQL formatter coding style conventions start with the comment conventions. Some basic conventions are how to handle multi-line comments. It’s typical to prefer multi-line comments over multiple sequential single line comments.

The spacing between the comment character and the start of the actual comment improves readability. Speaking of spacing, whitespace conventions are also important. The debate over spaces and tabs is a long-standing one, regardless of opinion; we’ve to choose one or the other.

The length of the code is important for readability. The longer the lines of code, it’s harder to maintain the standards.

A constant represents a data value. The format of the constant always depends on the data type. For example, the character string value must be enclosed in a single quote and Unicode string values must start with N’value’.

The use of reserved words is important. No objects should be defined with a name that matches reserved words. If exists, the objects must be referred using delimited identifiers.

As we know that there’s no semantic meaning in defining the empty lines and they’re typically limited to just one or two. The line endings or new-line characters should always be consistent. Whitespace also matters in statements, including block statements. For example, let’s start with the question, Should there be a space or no space between the parentheses and a brace? There’s a similar question for control statements as well. Should there be no space after a control statement or use a space? There’s no functional difference between the two but consistency and readability are important

T-SQL code maintenance

As we all agree that writing a code is different from maintaining a code. There are three questions that can determine if the code is maintainable or not.

  1. The first, can you maintain the code?
  2. The second, can someone else maintain it without asking for help
  3. Finally, can someone else read the code and understand the design and intent

If the answer to all three questions is yes, then the code is maintainable. It’s a good practice to do a peer review. If they can understand the design and intent, then the code is maintainable. You can take steps to improve the maintainability of your code using SQL formatter tools. For example, the use of consistent formatting, logical naming, clear and appropriate comments, functional documentation helps to understand the code to a greater extent.

The easiest way to improve code quality is by defining the coding convention. A SQL formatter convention is a set of guidelines and recommendations for writing a T-SQL code.

The SQL formatter coding conventions typically consist of three things; the first is a T-SQL coding style, which usually deals with the readability of T-SQL code, the second, practices or ways to build the T-SQL code and the third, the methods or process in which we implement a T-SQL code.

The 3rd party, a SQL Formatter tool

In this section, we’ll discuss 100% free —ApexSQL Refactor. This is a SQL code formatter tool, a free-add in, easily integrated with SQL Server Management Studio (SSMS) or Visual Studio (VS), and facilitates effective management of SQL Code using SQL Formatter Profiles.

Let us discuss some of the high-level features of ApexSQL Refactor—a versatile SQL Code query formatter tool.

  • Easy installation
    • It’s a SQL Server Management Studio or Visual Studio add-in. Download the binary (~24 MB) and proceed with the installation steps. The installation steps are pretty simple and straight-forward. Make sure you close all the SSMS open session.
  • To locate ApexSQL Refactor, open SQL Server Management Studio(SSMS) and Click ApexSQL menu

1

  • Let us place the unformatted complex SQL in the SSMS New Query Window and click Format SQL by browsing the ApexSQL Refactor You can also use a keyboard shortcut (Ctrl+Shift+Alt+F).2

On the right, you can see a completely formatted SQL.

 

3

I have converted one of the largest unformatted Stored Procedure (13 K rows) into an SQL formatted code. It took less than a second to complete the conversion. We all agree that the formatting of such a huge length T-SQL code is never be an easy task.

  • Next, Format SQL by Profile. To view the setting detail of the default Profiles, click Options…

4

  • In the options page, you can select the Profile and view the default settings of each profile. You can refer the article for more information about each of the profile in detail here
    • ApexSQL
    • Compact
    • Extended
    • MSDN SQL BOL

5

  • The built-in profiles use a default template. In case of customization, you can copy the selected built-in profile using the Copy button and then you can change in the desired way. In this case, the ApexSQL default profile is copied to ApexSQL – BI_DW and modified the settings in the way it is useful.

6

  • Now, you can see that the profile is listed under the Format SQL by profile option.

7

  • Let us take a look at the Other formatting This feature is very interesting as you can generate a formatted SQL code for specific or the entire database objects.
    • Let us walk-though the steps to generate Formatted SQL for the Adventureworks2014 database.
      • Select the AdventureWorks2014 database and right-click and select Format SQL objects…8
        • Now, you can see a Format SQL Objects… window. This gives us a control to select the objects that you want do SQL formatting. It has an option to sort the grid. So that objects can be sorted and selected with fewer clicks.
        • You can also compare the SQL formatted code v/s SQL unformatted code by selecting the desired SQL object and right-click and select the Compare button
        • After the selection, click Create script…button to copy SQL formatted script to the new query window9
        • Next, let’s take a look the select Format SQL Scripts… option.14.png
          • In this option, navigate to the SQL script file and choose the SQL formatter profile. In this case, ApexSQL is the profile, and you can leave the default SQL formatted file name or customize as per the requirement and then press the OK button
          •  1112
            • The code is SQL Formatted as per the selected profile. You can browse the path to view the Formatted SQL code.

13

That’s all for now…

Wrap Up

Thus far, we discussed a lot of standards, rules, guidelines, and 3rd party few SQL formatter tool for SQL code formatting.  I would like to conclude stating the way you format T-SQL is entirely up to you. It’s a matter of style, not substance, but it is important and vital for several reasons that we already discussed in this article. ApexSQL Refactor is 100% free SQL formatter tool for both personal and corporate use.

Feel free to use it, but also feel free to develop the style that works for you. However you choose to format you’re SQL, it’s important that you find it readable, and that you apply your rules consistently. I prefer ApexSQL Refactor and saved a lot of manual hours.  How about you?. I hope, it will save your time as well. Thanks for reading and feel free to comment below…

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s