• SQL Prompt

How to implement a T-SQL code formatting standard using SQL Prompt

To what extent should a team attempt to impose a uniform SQL coding format? It can sometimes seem like a futile task. All programmers have a preferred formatting style, to which they profess an unwavering attachment. Check the code of ten programmers, though, and you will likely discover at least 15 different formats, since few developers are particularly consistent in applying their preferred style.

Just a note – if you’ve upgraded to SQL Prompt v8, Louis has updated this blog post to reflect the upgraded formatting features. Find out more about how the brand new formatting engine lets you set styles – and switch between them in one click.

If those ten programmers are coworkers on the same project, how do you agree on a standard style, 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 the rest.

If that doesn’t sound like fun, here’s my suggested alternative. First, agree on the details of how exactly code should be formatted. If you fail to reach a consensus, then you use the ‘Louis Davidson’ style, the basics of which I demonstrate in this article, as a starting point. Or maybe just print it out and then beat people around the head with it until they submit.

Second, use a standard code formatting tool, in this case SQL Prompt, to save a defined style as a shareable template, so that every programmer can apply it to their SQL code with a few clicks.

Why impose a SQL formatting standard?

Every programmer likes to read code that’s set out in a certain way. A programmer’s individual style is influenced by the formatting preferences of the first language they learned. A Java programmer will tend to format SQL as if it were Java, for example, 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.

So why even attempt to impose a single SQL formatting standard? 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 deciding exactly what your team’s standard will be. It’s worth enduring the the effort and the occasional tantrum required to reach an agreement. As Oscar Wilde may have said, had he been a programmer: The only thing worse than a code formatting standard is no code formatting standard.

Choosing a SQL formatting standard

Achieving any sort of standardized formatting, while the team are writing code, is a tremendous pain, largely because SQL can be formatted, and still be readable, using countless different formatting options and styles.

SQL was always intended to be close to a real declarative human sentence, and while many believe it ought to be formatted as such, one can take this too far, making the code look like the writing of an author shunning all proper punctuation and capitalization.

Listing 1 shows a simple SELECT statement, using the WideWorldImporters sample database. The code will execute, and returns the twelve states in the US that have a city named Nashville, but I think most would agree that the formatting is atrocious, based simply on the idea of fitting as much code on a line as possible and letting things wrap:

Listing 1

If we can agree that there are very few (employed) programmers that would adopt this zero formatting approach to code, I bet we can’t agree on how exactly it should be formatted. There are a lot of choices to consider, such as:

• How to format and align the main clauses and sub-clauses – Personally, I like primary clauses aligned on the left, and everything else indented to line up on the clause.
• System Function/Keyword capitalization – Having them capitalized makes it clearer where the built-in elements are located, even if it’s a pain to type manually.
• Number of columns per line in the SELECT clause – Either one column per line, or as many as will fit on a line. I generally like the code to be as compact as possible without wrapping on a standard programmer’s monitor.
• Alias style–I typically prefer using expression AS Alias, but I have acquaintances that have argued strongly for Alias = expression.
• Leading or trailing commas – Commas, much like in writing, are another place where strong opinions are exposed. I will use trailing commas as this is usually the desired approach.

I could go on further, but this should be enough to establish that there are a ton of choices one could make when formatting code. Expand the question to all DML and DDL including tables, procedures, functions and so on, and your team members could find a million formatting variations for each piece of code.

For what it’s worth, Listing 2 offers this writer’s opinion on how the query should be formatted. At this stage, apart from adding an AS for the alias and a semi-colon terminator, I’m restricting myself to making the code more readable only, without substantially changing the text. At this stage, I’m avoiding the urge to make this better code (rather than just better looking code), such as by using proper object qualification and aliasing on all column names:

Listing 2

Having agreed on how the SQL code should be formatted, the next job is to ensure that any code made available for consumption by the team has the formatting standard applied. This is where a tool such as SQL Prompt can help. In the coming sections, I’ll demonstrate how to use it to implement a basic, minimal coding standard for sharing with the team.

Of course, you still need every team member to use the tool, and apply the standard in a consistent manner. Enforcement will mostly be achieved with code reviews and teamwork.

SQL Prompt Formatting Options

After installing SQL Prompt, it’s available directly from the SSMS menu. I’m using the latest version, which at time of writing was v7.5.0. If you want to see the formatting it applies, right out-of-the-box, simply paste into SSMS the poorly formatted code in Listing 3:

Listing 3

Highlight the code and choose Options in tool menu to see what formatting options the tool offers. Figure 1 shows the overview Styles screen.

Figure 1

Down the left-hand side, you’ll see many options to control how SQL Prompt makes suggestions, formats code as you type it in, applies formatting to existing code, and helps to manage your various tabs in SSMS.

In this article, we’ll focus only on the Format section, but note that within the Labs | Experimental Features section of Figure 1, you’ll find the option to enable some new formatting styles that allow more granular control over formatting. I don’t have these options enabled for this article, as I wanted to offer recommendations for a basic, stable formatting ‘template’ using only the current options.

At the top of the main screen in Figure 1, you’ll see that you can save different named styles, in addition to the default style. This is a very useful feature, as it means you can save different styles for different uses. For example, you can define your personal formatting style for when you’re developing code, the team formatting style to apply 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. There’s also an option to Export a saved style, for sharing with team members (we’ll come back to this shortly).

The Sample SQL tab demonstrates how the selected named style (in this case, default) formats various statements, clauses and conditions. On the Current Query tab, you can see a preview of the code from Listing 3, as formatted in the ‘default’ style:

Figure 2

Defining the basic Louis Davidson Custom style

In this section, I’ll attempt to define my base formatting style template. If I want to create another template for a very specific purpose, such as comparing different versions of the same block of code, I’ll create a new custom style from this base style.

With the Default style selected, click Save… and enter a new style name, in my case Louis_BaseStyle.

Figure 3

This creates a new custom style, but one that is currently identical to the Default style, so it’s time to make some customizations to get the code looking exactly as I prefer.

Some changes, such as the ones we will make in the Actions tab may not show up in the preview, so you need to exit and format the code to be sure that the formatting is exactly what you want. As an example, Insert semicolons is set by default, but does not show up in the preview.

I’ll walk through each set of options within the Format section where I’ve chosen to adapt the default settings.

Actions

• Check the following boxes:
  • Add/remove square brackets
    • Remove unnecessary brackets – our sample code doesn’t have any but trust me, unnecessary square brackets are heinous!
  • Add/remove AS keyword on alias definition for tables and views
    • Add AS Keyword
  • Apply column alias style
    • using the Alias style, column AS alias

Data statements

• Place the following on a new line:
  • Check Join condition – don’t select Align join condition with JOIN condition
  • Uncheck Each subsequent column

• Indents:
  • Align SELECT with INSERT – another construct we have not used, but about which I feel strongly!

Commas and Parenthesis

• Uncheck the Add space before comma box within the Place commas at end option

Applying the custom style

You can see a preview of the custom style by navigating back to Styles within the Options screen. Let’s now apply the style to our code in SSMS. Close the Options dialog (there is no need to resave your work, assuming you saved the custom style at the start with a new name).

In SSMS, use Control K, Control Y, or select Format SQL from the SQL Prompt menu. Now the code looks as shown in Listing 4:

Listing 4

After you apply the formatting, changes are highlighted in the editor to show you that semi-colons are added, the AS was added to the StateName alias, and DESC was capitalized.

This code looks much better than it previously did, but there are a few things that you may want to do manually, such as adding a CarriageReturn-LineFeed (CRLF) between statements. For example, you probably would want to change:

To:

By the same token, if you had ten CRLFs between the statements, SQL Prompt would not remove any of them.

Sharing custom styles

Once you have your style configured, you can export it and distribute to the team. The Export option next to the Style dropdown box exports just the custom style, which is what we want to use here. You can save the file anywhere, perhaps to a shared file location to which your team has access.

Figure 4

The Import… and Export… options at the bottom of the Options dialog will import and export all Prompt settings, including Suggestions, Inserted Code, Tabs, etc., along with the Format settings that are in use. However, where a coding formatting standard will be unique for the team, some of the other settings may be a more personal choice.

Any team member who has SQL Prompt installed (like, all of them!) can go into the options and import it, from the Format | Styles section of the Options dialog, as shown in Figure 5.

Figure 5

They can apply custom style to all the code they are working on without losing any of their existing formatting styles (unless they have a custom style with the same name as the one being imported, in which case they will deserve to lose their style for ignoring the warning messages.)

Note that to implement your complete strategy using SQL Prompt to shape your code, you will likely want to sync up some of the other options of the tool with the team. For example, the Inserted Code section will control how object names are qualified, how table aliases are generated, and so on.

Summary

Many teams avoid the topic of a standard code formatting style as it is too much work to get everyone using the same format. Determining what becomes the team formatting standard is almost certainly going to be a long, possibly contentious process, but once it is done, applying the common formatting style, consistently, will be very straightforward using a tool such as SQL Prompt. Hopefully, the basic ‘Louis Davidson’ style I’ve suggested in his article will form a good starting point.

My next article will show how to use custom styles to support not just one standard format, but multiple ones, for diverse purposes. For example, you might want a formatting style to make comparing changes to complex code easier. Alternatively, if you hate the corporate standard, you can apply your own formatting when making changes, and change it back before checking the code into source control.

SQL Prompt Pro lets you write, format, refactor, and share SQL effortlessly. A part of the SQL Toolbelt, it encourages collaboration and teamwork, while stripping away the repetition of coding.

If you’re not a SQL Prompt Pro user, download a free trial and see how much more productive you can be writing code every day.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more