Table of contents
- T SQL format options
- Impact preview
- SQL formatter profiles
- Importing/Exporting SQL formatter profiles
- Format object in a batch
- Format T-SQL scripts in a batch
- Host integration
- High DPI and 4K resolution support
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 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 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, a free 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.
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:
- Rules of SQL formatting – Commas and spacing in T-SQL
- Rules of SQL formatting – Regular and delimited T-SQL identifiers
- Rules of SQL formatting – SQL code commenting
- Rules of SQL formatting– SQL naming conventions and capitalization rules
- Rules of SQL formatting – Terminating SQL statements with semicolons
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.
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.
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.
Finally, one variable that makes a good SQL formatter even better is cost. Tools licensed at $300 or more, per user, can result in bills of thousands of dollars, for large teams.
ApexSQL Refactor is 100% free SQL formatter tool for both personal and corporate use.
- SQL formatter standards – Capitalization, Indentation, Comments, Parenthesis
- SQL formatter standards – joins, lists, structure, operations
- SQL formatter basics – How to improve readability with capitalization and object naming
- SQL formatter basics – formatting to Microsoft standards and guidance
- List of top SQL formatter tools
July 4, 2018