Table of contents
- Formatting options
- Impact preview
- Formatting profiles
- Importing/Exporting formatting profiles
- Batch SQL objects formatting
- Batch SQL scripts formatting
- 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 and troubleshooted 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 formatting SQL code is that there is no style or formatting 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 formatting 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 SQL code quickly and efficiently.
This article will describe and introduce some basic guidelines and expectations for what a perfect SQL Server formatting tool should have in order to meet your goal to establish a consistent and standard SQL formatting style (standard) and how to apply that SQL formatting style to one T-SQL script or many T-SQL scripts or objects in one click.
One such tools 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 formatting options and features for applying SQL formatting style on one or bulk T-SQL scripts at once.
Formatting 1001 is basic formatting options. One of the major concerns when it comes to establishing SQL formatting 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 current formatting 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 choose the beautification (formatting) tool is one that can handle all those different preferences and ApexSQL Refactor is the exactly that tool.
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';
One of the critical things that every SQL Server formatting tool should have is to be able to visually preview how the SQL code looks like after adding some formatting options.
ApexSQL Refactor has SQL code formatting impact preview which shows a clear insight and view of the impact of any formatting option changes immediately when you make them. If you don’t like how it looks it can be easily changes 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 SQL formatting style.
Formatting 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 SQL formatting tool needs to have options for settings these formatting rules.
ApexSQL Refactor has all these options to meet your requirements. More about SQL formatting 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
Being able to set a desired SQL formatting style, saving that style and sharing the SQL formatting style with colleagues is another thing that every SQL Server formatting 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 formatting options to a reusable profile
- group and categorize formatting styles
- quickly modify existing profiles
- avoid formatting conflicts with users
In addition, ApexSQL Refactor comes with four built-in formatting profiles
- ApexSQL – contains what we determined was a good SQL formatting standard
- Compact – sets indentations option to 0 (zero) space, all additional spaces are unchecked and empty lines are removed for a query which formatted SQL code looks dense
- Extended – is opposite of the Compact formatting profile. This profile adds empty lines before/after every statement, comments. Add space around arithmetic, logical operators
- MSDN SQL BOL – mimics formatting rules used in the MSDN resource site
Those four built-in profiles cannot be changed in any way, but a copy of these built-in formatting profiles can be made by clicking the Copy button and then can be changed in the preferred way:
Also, the new formatting can be created by clicking the New button:
Importing/Exporting formatting 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 formatting profiles and share with colleagues, press the Export button on the Options window:
To import formatting 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.
Batch SQL objects formatting
Once the formatting profile is created and formatting options are set, formatting SQL code can begin in earnest, with formatting one object or script at a time. But what about formatting everything in a database at once?
Formatting a lot 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 SQL formatting tool should have a feature that can format multiple SQL objects with 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 formatting 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 SQL formatting 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 SQL formatting profile:
Additionally, before formatting a SQL object script with desired SQL formatting profile, the same SQL scripts can be shown so the user can see how it will look like after formatting. In this way, it can be compared by selecting a desired SQL object from the formatting SQL list, right click and select the Compare button:
Batch SQL scripts formatting
One more thing that needs to be taken into consideration, when choosing the right SQL Server formatting 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 SQL formatting 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 formatting profile under the Profile combo box and press the OK button:
All code in the select SQL scripts will be formatted with desired SQL formatting style which reviewing of SQL scripts makes much easier.
Another important item when choosing right formatting 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 formatting 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 formatting options and formatting 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 formatting tool even better is cost. Tools licensed at $300 or more, per user, can result in bills of 10’s of thousands of dollars, for large teams.
ApexSQL Refactor is 100% free for both personal and corporate use.
- SQL Formatting standards – Capitalization, Indentation, Comments, Parenthesis
- SQL Formatting standards – joins, lists, structure, operations
- SQL query basics – How to improve readability by formatting capitalization and object naming
- How to format SQL like a pro – formatting to implicit Microsoft standards and guidance
July 4, 2018