Top things you need in a SQL formatter tool

Table of contents

Nobody likes to check and read a ton of messy, unformatted text. When it comes to read a complex SQL code that problem can become even more difficult, especially if you are the one who has to untangle it.

SQL code can be formatted with a style making the reading of it easier, especially if the entire team uses the same. Clean, well formatted (consistent) SQL code can be read much faster than inconsistently written SQL code, whether the inconsistency is across team members and different objects or even within the same script of the object itself. Well organized and formatted SQL code can be reviewed more efficiently. Handing off a project to another team or a client with well formatted SQL code will be much smoother and much more appreciated.

The main problem with SQL script formatting is that there is no style or standards of how the SQL code should be formatted and all is generally a matter of preference. The best solution here is to set the standards which you and your team in the company will stick to, and then make sure they are applied consistently.

All SQL code can be formatted manually, which is very time consuming but 3rd party tools do exist and can be used to format quickly and efficiently.

This article will describe and introduce some basic guidelines and expectations for what a perfect SQL query formatter tool should have in order to meet your goal to establish a consistent and standard Format style (standard) and how to apply that style to one T-SQL script or many T-SQL scripts or objects in one click.

One such formating tool is ApexSQL Refactor, add-in which can be integrated in SQL Server Management Studio (SSMS) or Visual Studio (VS). ApexSQL Refactor has over 200 SQL query formating options and features for applying a style on one or bulk T-SQL scripts at once.

Options

The first goal is setting basic options. One of the major concerns when it comes to establishing standards is which T-SQL keywords should or should not be capitalized in the code.

Under the Capitalization tab of the Options window, capitalization rules can be set for the following:

  • SQL keywords – Select, Update, Insert, Delete, Declare, etc.
  • Data types – int, varchar, data, nchar, etc.
  • Identifiers (Object names) – name of the views, tables, stored procedures, etc.
  • System functions – Avg, Left, Len, Substring, etc.
  • Variables – @@Servername, @Firstname

To set appropriate rule for capitalization, the following options can be used: UPPER CASE, lowercase, lowerCamelCase, UpperCamelCase, Proper Case, or leaving the default SQL query layout rules by deselecting the option:

Some developers prefer SQL keywords, datatypes, etc. to be in UPPER CASE and other developers, however, like the SQL keywords to be in lower case.

The best solution when choosing a tool is selecting one that can handle all those different preferences.

In the first example, keywords are upper case and data types are lower case: 

DECLARE @Initials varchar(5);
DECLARE @FirstName varchar(25);
DECLARE @LastName varchar(25);
SET @FirstName = 'Katie';
SET @Lastname = 'Melua';
SELECT @Initials = SUBSTRING(@FirstName, 1, 1) + SUBSTRING(@Lastname, 1, 1);
PRINT @Initials;
SELECT *
FROM HumanResources.Employee;
WAITFOR TIME '00:00';

In the second example, keywords are lower case and data types are upper case: 

declare @Initials VARCHAR(5);
declare @FirstName VARCHAR(25);
declare @LastName VARCHAR(25);
set @FirstName = 'Katie';
set @Lastname = 'Melua';
select @Initials = SUBSTRING(@FirstName, 1, 1) + SUBSTRING(@Lastname, 1, 1);
print @Initials;
select *
from HumanResources.Employee;
waitfor time '00:00';

The third example, keywords and data types are upper case: 

DECLARE @Initials VARCHAR(5);
DECLARE @FirstName VARCHAR(25);
DECLARE @LastName VARCHAR(25);
SET @FirstName = 'Katie';
SET @Lastname = 'Melua';
SELECT @Initials = SUBSTRING(@FirstName, 1, 1) + SUBSTRING(@Lastname, 1, 1);
PRINT @Initials;
SELECT *
FROM HumanResources.Employee;
WAITFOR TIME '00:00';

SQL formatter Impact preview

One of the critical things that every SQL layout tool should have is to be able to visually preview how the SQL code looks like after adding some options.

ApexSQL Refactor has a SQL code format impact preview which shows a clear insight and view of the impact of any option changes immediately when you make them. If you don’t like how it looks it can be easily changed and reverted without saving:

Setting the rules for capitalization of SQL keywords, data types, identifiers (Object names), system functions, variables is just one item in the set of the style.

SQL code layout rules for aliases, comments, commas, spacing, aligning, etc., needs to be set as well in order to have a good, consistent, readable and well formatted SQL code.

For all the above-mentioned things a Format tool tool needs to have options for settings these SQL query styling rules.

ApexSQL Refactor has all these options to meet your requirements. More about SQL formatter options can be found from the links below:

SQL query formatter profiles

Being able to set a desired style, saving that style and sharing the style with colleagues is another thing that every SQL Server formatter tool needs to have. Also, having a pre-set selection of pre-configured profiles, out-of-the-box is very helpful.

With ApexSQL Refactor users can:

  • save preferred SQL query formatter options to a reusable profile
  • group and categorize formatting styles
  • quickly modify existing profiles
  • avoid conflicts with other users

In addition, ApexSQL Refactor comes with four built-in profiles

  • ApexSQL – contains what we determined was a good SQL formatter standard
  • Compact – sets indentations option to 0 (zero) space, all additional spaces are unchecked and empty lines are removed for a query where the SQL code looks dense
  • Extended – is opposite of the Compact profile. This profile adds empty lines before/after every statement, comments. Add space around arithmetic, logical operators
  • MSDN SQL BOL – mimics the style used in the MSDN resource site

Those four built-in profiles cannot be changed in any way, but a copy of these built-in SQL query formatter profiles can be made by clicking the Copy button and then can be changed in the preferred way:

Also, a new SQL formatter profile can be created by clicking the New button:

Importing/Exporting SQL format profiles

Once you’ve create the perfect profile, it is important to be able to export it to make it accessible others; as well as for the recipients of the profile to be able to easily import it. ApexSQL Refactor provides just this functionality

To export profiles and share with colleagues, press the Export button on the Options window:

To import profiles on the Options window, press the Import button.

More about ApexSQL Refactor profiles can be found on the How to customize SQL formatting profiles page.

Processing objects in a batch

Once the format profile is created and options are set, formatting SQL code can begin in earnest, with one object or script at a time. But what about processing everything in a database at once?

Processing a lot of SQL objects like stored procedures individually can be a very difficult and time-consuming job, of course.

So, another thing, we can add is that each format tool should have a feature that can format multiple SQL objects with a single, desired SQL profile at once.

ApexSQL Refactor has the Format SQL objects feature that allows exactly that. In Object Explorer, select a database in which are located the objects that want to be formatted, right click and, from the context menu, choose the Format SQL objects command:

All script based objects, e.g. view, stored procedures will be listed in the Format SQL objects window:

Under the Profile combo box, select the desired SQL formatter profile (e.g. ApexSQL) and, from the list, choose the SQL objects that want to be formatted, click the Create script button, which will open a script in the Query editor, then click Execute (F5) button:

To confirm that SQL objects are formatted with desired profile, go back to the Format SQL objects window, under the Profile combo box, choose a profile and, in the SQL objects list, under the OK column with green check mark icon, SQL objects that are formatted will be indicate, all other SQL objects will be marked with red x icon which indicates that they are not formatted with the selected profile:

Additionally, before formatting a SQL object script with the desired profile, the same SQL scripts can be shown so the user can see how it will look like after it is processed. In this way, it can be compared by selecting a desired SQL object from the list, right click and select the Compare button:

Batch scripts T-SQL format

One more thing that needs to be taken into consideration, when choosing the right SQL layout tool, is the possibility to format SQL scripts and scripts in folders in batches.

In situations when, on the machine, there are a lot of SQL scripts which code needs to be checked and that code is in a pretty big mess like in the image below:

Checking code in these the SQL scripts can be challenging.

One solution for this is to open the SQL script in SSMS and format it with your preferred profile by clicking the Format SQL button:

This solves a problem for one SQL script, but for more of these SQL scripts, a quicker solution needs to be provided.

ApexSQL Refactor offers the Format SQL scripts feature as a solution for this kind of a problem. From the ApexSQL Refactor menu, under the Other formatting sub-menu, select the Format SQL scripts command:

The Format SQL scripts window will open, in the list navigate and select the SQL scripts, choose the desired SQL formatter profile under the Profile combo box and press the OK button:

All code in the select SQL scripts will be formatted with desired SQL formatter style which reviewing of SQL scripts makes much easier.

Host integration

Another important item when choosing right SQL styling tool is its direct integration into host applications that you normally use for SQL coding like SSMS or Visual Studio.

Luckily, ApexSQL Refactor supports integration from SSMS 2012 and above and supports integration into Visual Studio from VS 2010 version and higher.

This means you can add powerful SQL formatter capabilities and improve you coding productivity without ever leaving the home of your favorite editor

Seamless host integration and consistent user experience

Integrating into a host application is one thing. Integrating seamlessly with a consistent end user experience is another. Many add-ins are bad guests, hogging menu space, offering disjointed colors and icons and generally being visually “loud” and intrusive. ApexSQL Refactor is a polite guest that seamlessly blends into host environments, almost like it was part of the IDE itself. This consistent user experience includes iconography, visual style and themes.

Color themes allow the user to modify the visual appearance to suit your preferences and good 3rd party tool should match this capability. ApexSQL Refactor supports SSMS and Visual color themes but better yet will automatically change to adapt to host theme settings.

See this article to learn more.

Visual Studio dark color theme:

SSMS blue color theme:

One note, ApexSQL Refactor supports SSMS dark color theme:

Even that the color theme is not official yet; for those who want to know how to set up the dark color theme in SSMS 17 can be found on the Setting up the dark theme in SQL Server management studio page.

Furthermore, all icons that appear in commands, dialogs, templates, etc. in ApexSQL Refactor are inherited from the Visual Studio Image Library. Some of the icons from the library are redesigned to be more intuitive, simple and are easily recognized symbols.

More can be found on the Visual Language Dictionary for Visual Studio for iconography, colors and other visual concepts applied in ApexSQL tools/add-ins page.

High DPI and 4K resolution support

ApexSQL Refactor supports high DPI and 4K resolution which means when the ApexSQL Refactor is run, windows (e.g. Options window) in which you set all SQL formatter options and the screen will not be blurry on your 4K monitor:

Note: VS handles 4K and high-DPI very well, but SSMS does not work so well on 4K monitors. For the 4K and high-DPI problem in SSMS 2016 and higher there is a solution which can be seen on the How to enable High-DPI in SSMS (SQL Server Management Studio) – Tips, tricks and issues page.

See also:

 

Related posts:

  1. How to quickly convert SQL code to language-specific client code
  2. SQL code refactoring – ways to improve database performance (part III) – Add surrogate key
  3. Rules of SQL formatting – SQL code commenting
  4. SQL database refactoring – Finding external references by analyzing object dependencies
  5. SQL query basics – How to improve readability by formatting commas, spacing and alignment
July 4, 2018