Louis Davidson

8 June 2017

• SQL Prompt
• SQL Formatting and Styles

How to implement a SQL formatting standard using SQL Prompt v8

With ten programmers working on the same project, how do you agree on a standard style for formatting SQL code, and then implement it consistently? One way is through draconian rules, meeting after meeting, and the occasional sacrifice of a programmer to put the fear in those who remain.

If that doesn’t sound like fun, here’s my suggested alternative:

1. Decide exactly how you want to format SQL code.
2. Use a standard code formatting tool, in this case SQL Prompt v8, to define the team style and save it in sharable form, so that every programmer can apply it to their SQL code with a few clicks.
3. Give the developers the flexibility to apply their own styles, while working privately, and to use different styles for different purposes, and then switch back to the agreed style, before sharing their code with others.

This broadly is what I’ll cover over this and two subsequent articles, starting here with why your team needs a standard style for formatting SQL and, broadly, how SQL Prompt will help make this happen.

These three articles will cover only the formatting styles of SQL Prompt, and creating custom styles to apply to existing code. I will cover in separate articles SQL Prompt’s auto-complete options, which control the suggestions it makes and the formatting options it applies as you type, and its capabilities that extend beyond basic formatting, to code analysis and refactoring.

Why impose a ‘team style’ for SQL code?

Every programmer has a preferred formatting style, and likes to read code that’s set out in a certain way, often influenced by the formatting preferences of the first language they learned; a Java programmer will tend to format SQL as if it were Java, and use a rather different style than a native Perl programmer. Other factors have an influence too, such as the size of the device on which the programmer generally views the code.

Check the code of ten programmers, though, and you will likely discover at least 15 different formats, since few developers are consistent in applying their preferred style.

So why even attempt to impose a single SQL formatting standard? It can seem like a futile task, but here are two good reasons:

• Ease of reading – If everyone’s code is formatted the same way, it will be much easier for any team member to review another’s code with minimal confusion. Complex Boolean expressions, particularly in WHERE and JOIN conditions, can be hard enough to fathom without the added complication of inscrutable formatting.
• Ease of code comparisons – Often you want to compare different two versions of the same block of code and work out what changed. That’s a much harder task if the person who revised the code used an entirely different formatting style from the original author.

Having established the need for some consistency, the hard part is agreeing on the details of the team style. Achieving any sort of standardized SQL formatting across a team of programmers is hard, because SQL can be formatted, and still be readable, using countless different formatting options and styles for the various clauses, sub-clauses, column listings, join clauses, aliases, functions, expressions, and so on. On top of that, we need to make it easy for everyone in the team to implement the agreed style, consistently.

It’s worth enduring the effort required to reach an agreement. As Oscar Wilde might have put it, had he been a programmer: The only thing worse than a code formatting standard is no code formatting standard.

A SQL code formatting tool such as SQL Prompt makes this task easier because it allows the team to define a style they like and then save it as a custom style, in a shareable format.

Formatting SQL with SQL Prompt

After installing SQL Prompt, it is available directly from the SSMS menu, in the same place you write your code. I’m using the latest version of SQL Prompt, which at time of writing was v8.01. Compared to previous versions, v8 offers a lot of new options to control how formatting is applied to your code. These new options were available as Experimental Features in SQL Prompt v7 (under Options).

Formatting out of the box

To see the formatting SQL Prompt applies, out of the box, paste the poorly formatted code from Listing 1 into a query window in SSMS:

Listing 1

We apply formatting by invoking the Format SQL command, from the SQL Prompt menu, or from the context menu opened by right-clicking in the query window, or via the Ctrl + K then Ctrl + Y keyboard shortcut. When we run this command, SQL Prompt will apply to the code all the style options associated with the active formatting style (in this case the pre-installed Redgate Default style), plus any activated formatting actions (covered shortly).

Listing 2 shows the resulting, formatted code:

Listing 2

As you can see, this is immediately a lot more readable. It’s still not perfect, to my eyes at least (more on that in the next article), but it’s a big improvement.

Let’s look at the details of this formatting. I’m only going to review them briefly, as they are covered in detail elsewhere.

The formatting styles

SQL Prompt allows us to define styles, where each style (akin to a template), encapsulates a specified set of values for options that govern how we wish to format SQL code. From the SQL Prompt menu, select Edit formatting styles to bring up the Formatting styles dialog.

Figure 1

You can see five pre-defined Redgate styles, each one representing a common, different way to style SQL code, as well as a Your Styles section, which displays your own custom styles. I have just one custom style, currently, called Louis style. It’s worth checking the Preview current query checkbox, so you can preview the formatting for each style, within the formatting styles dialog, simply by clicking on it.

Currently, the Redgate default style is set as the active style, so by running the Format SQL command, as previously, SQL Prompt applies all the code formatting options specified by this default style. Within the Formatting styles dialog, we can switch to a different active style simply by clicking on a different style and selecting Set as active via the vertical ellipsis.

As part of the upgrade to SQL Prompt v8, from an older version, SQL Prompt will automatically convert all existing styles (.sqlpromptstyle) over to the new style (.sqlpromptstylev2), and save them in the user’s Style folder, which by default is located at \%LOCALAPPDATA%\Red Gate\SQL Prompt 8\StylesV2. They will appear automatically in the Your Styles section.

How to import old styles post-upgrade

What if another SQL Prompt user, still on an older version of the tool, sends you a style that you want to import, after you’ve already upgraded to v8? For any custom style defined using the ‘experimental’ version of the new styles in SQL Prompt v7, locate the style, rename it with the ‘v2’ extension and copy to the style folder. For custom styles defined using the old-style format, you’ll probably want to redefine these styles from scratch, making use of the greater versatility offered by the v2 style features. However, if you do wish to auto-convert an old style, there is a roundabout manner to make this happen: ‘Downgrade’ to SQL Prompt v7, place the style in the style folder, then upgrade again to v8, at which point it will be auto-converted.

Creating a new style

There are several ways to create a new custom style. Under Your Styles, click + Create a Style to open the Create a style dialog, then name your new custom style and specify the style on which it will be based, which by default will be the Redgate Default style.

Figure 2

Alternatively, you can choose Copy… from the vertical ellipsis next to the style on which you wish to base the custom style. Since it’s not possible to edit directly any of the pre-defined Redgate styles, if you select one of these styles and then hit Edit style, or double click on any of these styles, you’ll be prompted to create a new style, based on the selected Redgate style.

Once you select Create style, or if you Edit, or simply double click, a style listed under Your Styles, you’ll open the Custom style dialog, where you can define all the style options for the selected style.

The style options

When you run Format SQL, SQL Prompt will apply all the style options defined for the active style, plus any of the formatting actions that are currently activated (covered in the next section). Figure 3 shows the style options for MyCustomStyle, without making any further changes:

Figure 3

SQL Prompt allows us to control the layout and formatting of the various statement, clauses, and expressions that comprise a typical SQL command.

For example, under Global | Whitespace, SQL Prompt control over tab behavior, line wrapping, and lets you determine the number of empty lines between statements, using the New lines setting. The Statements section provides close control over how DML and DDL is formatted, as well the formatting of control flow statements such as BEGIN…END blocks, CTEs and so on. We can also control the formatting of joins, and of various types of expression.

You can edit these options to suit your, or your team’s, requirements and then click Save, and the new custom style will appear under Your Styles, and be set as the active style.

Figure 4

The formatting actions

Within the SQL Prompt Options dialog, the Format > Styles section defines all the actions that SQL Prompt will perform, on running Format SQL.

Figure 5

The checked actions will be applied to any active style, when we run Format SQL. By default, it will apply casing options (as defined within the style options for the active style) and insert any missing semi-colons. However, we can expand this to include other options such as removing or, begrudgingly, adding square brackets around identifiers, qualifying object names, and adding aliases. To return to the default settings for the formatting actions, just use the Restore defaults button in the top right of Figure 5.

The observed behavior on activating some of these formatting actions, will depend on settings elsewhere. For example, the behavior of Qualify object names will depend on the settings for Inserted code | Qualification. In a later article, we’ll look at using many more of the SQL Prompt options to create a complete code formatting configuration.

We can also apply these formatting actions, as well as many other types of action, on an ad-hoc basis, by highlighting the code we wish to format, in the SSMS query window, and using the Actions list, as demonstrated in Figure 6.

Figure 6

Adopting a custom style as the team standard

A good way to start is to review the style options for each of the Redgate pre-defined styles, choose the one that most closely matches what the team want, then copy it and customize it so that it meets your precise requirements.

Having defined and saved the custom style, you can give the whole team access to it simply by copying the style’s XML file (.sqlpromptstylev2) to a shared folder. Each member of the team simply changes their style folder to the shared folder.

Figure 7

Each user sets the agreed style as the active style and, when working in SSMS, applies the style using the Format SQL command.

Team members also have the flexibility to switch styles temporarily, for example if they find that a different style is better suited for a certain task. From SSMS, right-click in a query window and from the context menu, and use Active Style to activate a different style, and then Format SQL to apply it. This makes it very easy to switch smoothly between styles. Of course, this isn’t an excuse to avoid using the agreed team style altogether. Each user must remember to switch back to the agreed team style, and apply it, before checking the code into source control, or otherwise making it public.

Standardizing other SQL Prompt options and settings

Even if every member of the team works with the same shared style, there still may be differences in other settings, notably which formatting actions are activated. You will want to review the formatting actions and agree on which ones should be implemented, and therefore applied for any active style, on running Format SQL.

If individual team members temporarily change formatting action settings on an ad-hoc basis, then they will need to remember to reestablish the agreed settings, as well as reapply the team style, before making code public.

If you wanted to synchronize the settings for these formatting actions as well as the values for all other SQL Prompt settings, excluding the style option settings, for all developers, you could consider exporting and sharing with the team a ‘master’ version of Prompt.settings.

Figure 8

This exports a ZIP of all the XML configuration files found in \%LOCALAPPDATA%\Red Gate\SQL Prompt 8, except the LoggingConfiguration.xml file.

Figure 9

If these files are synchronized for all developers, and the team uses a standard custom style for formatting SQL, then all settings will be consistent.

Summary

SQL Prompt’s formatting styles can help you define and implement a standard SQL style across your development team. It also means each user can save different styles for different uses. For example, a personal formatting style for developing code, the team formatting style for when it’s time to commit the code to version control, or you need to ask another team member to review it, a ‘blog’ style when you want to share your code with others, and so on.

My next article will offer some specific guidance on the basics of what I think makes up a sensible code formatting style for SQL, and enshrine it in a custom SQL Prompt style. Hopefully, it will prove a good starting point for teams who have yet to implement an agreed style.

You can discover more about the new formatting style in SQL Prompt v8 by upgrading your current version, or downloading a 28-day, fully-functional free trial.