• SQL Prompt
• SQL Formatting and Styles

Using multiple custom SQL formatting styles in SQL Prompt

Usually, when coding SQL, you will want to go along with the coding style of the team you are working with. In my previous articles on SQL Prompt, I explained why a team of programmers might want to adopt a T-SQL coding standard, how SQL Prompt could make this easier, and then how to use SQL Prompt to define and share a custom SQL Prompt style (the Louis Davidson style), allowing everyone to enforce this excellent standard in their own code, with minimal effort.

There is just one problem with this, of course, and that’s that there are various reasons why each developer may want to stray, temporarily, from the prescribed standard. Perhaps they need a slightly amended style that makes it easier to compare two different versions of some complex code. Perhaps they need a different style for publishing code to their blog, or into a Word document, or email. Perhaps the agreed ‘corporate’ or ‘team’ style simply makes their eyes water. Similarly, if you are a consultant who works at multiple locations regularly, you may have a style for each customer, and one to format the code as you like it while you are changing it.

Your private development style

Standards are usually designed in two ways: by committee and by individual. Most of the time the only way you will be truly satisfied with a standard is if you are the one that created it, but the standard code formatting style for a team is more likely to be agreed by committee.

My previous article defined a sensible “base style” that would hopefully steer the committee in the right direction. However, let’s imagine some awful, parallel universe where your team’s agreed coding style means that T-SQL is styled as shown in Listing 1. To achieve this ghastly layout, I used SQL Prompt to define a classic custom style named Committee_Style; if you want to follow along and see how this was implemented, the style is available to download from here.

Listing 1

This code isn’t ‘wrong’ and executes just fine, but glancing even briefly at it hurts my eyes. Adopting a pleasing standard is hard enough, but working with a horrible format is next to impossible, so I’m going to make use of the Louis_BaseStyle when I am coding, and only when I am done will I apply the Committee_Style as necessary.

Switching between styles

You can switch easily between the Committee_Style and your preferred style, simply by setting the Active Style form the SQL Prompt menu, as shown in Figure 1.

Figure 1

After switching to your preferred development style, in my case Louis_BaseStyle, simply apply the Format SQL command. It will transform the code into something much easier on the eye as seen in Listing 2.

Listing 2

Of course, ignoring the coding formatting standard because an article told you to may have an adverse effect on your career plans, so remember to switch back to the agreed “committee” style, and apply it, before checking the code into source control, or otherwise making it public.

To do this, simply set the Committee_style as the active style and run Format SQL.

A style for comparing code

When comparing two versions of the same code, looking for meaningful differences, I like the SQL code to be laid out in a slightly different way than normal. I generally prefer code to be fairly tightly formatted, listing as many columns as possible on a line. However, when scanning code for differences, I prefer a much more “spaced out” style with the SELECT keyword and each subsequent column on separate lines.

Also, while I promote judicious use of comments in the code, they make spotting meaningful code changes harder (also, of course, some people ‘overdo’ code comments!).

In Listing 3, all I’ve done is add some verbose commenting to the code from Listing 2 and re-applied the Louis_BaseStyle.

Listing 3

Set the style options

Let’s create another custom style called Code_Comparison, copied from the Louis_BaseStyle previously referenced.

We’ll make a couple of changes, so that columns listed in the SELECT statement, and as part of the IN expression, are each on a single line:

• Global | Lists | List items
  • Place first item on new line: Always
  • Place subsequent items on new lines: Always
• Expressions | IN | Values
  • Place first value on new line: Always
  • Place subsequent values on new lines: Always
• Statements | Data (DML) | List Items
  • Place GROUP BY / ORDER BY expression on new line: Always
  • Place subsequent values on new lines: Always

Save the new custom style. Listing 4 shows how our code will look in the new style.

Listing 4

Those unruly comments will still make comparing code a bit messy though, so let’s deal with them next.

Removing comments

I’m a big fan of adding terse, meaningful comments to code, to help other users understand the intent of the code, or why certain choices were made. However, when you need to compare two versions of the same code, to understand the cause of behavioral differences, then comments, especially verbose comments, are unnecessary clutter.

Usually you will only remove comments temporarily, but not always; for example, some developers don’t like comments committed to source control, as it makes it harder to spot meaningful changes.

Removing comments is regarded as code refactoring, so it isn’t part of the style options, and nor is it one of the formatting actions that we can apply automatically when we run Format SQL. Instead, we simply remove them on an ad-hoc basis, using the Actions list. Within SSMS, run Format SQL to apply the Code_Comparison style, and then highlight the code and select the Remove comments action from the Actions list, as seen in Figure 2.

Figure 2

The final code will look as shown in Listing 5.

Listing 5

When you are finished reading the code, you can revert to the original version by using Ctrl + Z, or Edit\Undo, to reestablish the code comments, and then switching back to the original style and running Format SQL. Alternatively, you can just perform two undos, one for the comments, one for the formatting.

Summary

SQL Prompt’s custom styles makes it easier to apply different custom formatting to your code, depending on your current requirement, even if it’s just because you find the agreed, corporate code formatting standard impossible to read.

Even if your primary corporate SQL coding standard cannot be replicated using SQL Prompt, you can still use formatting templates to allow you to view code in an alternate style. When you’re done, you can simply revert to the committee style and run Format SQL.

However, if you want to make changes to the code that falls outside what’s defined within a custom style, for example using actions to remove comments to perform code comparisons, or to add or remove square brackets, then you’ll need to remember to “undo” these changes before, for example, committing the code back into the version control system. In such cases, it’s probably wise to make a copy of the code first, in a new query tab, so you can be sure you always have the original available. SQL Prompt’s Tab History feature (bar none, the one feature of SQL Prompt that I can no longer live without) will restore previous tabs, even if you close them without saving them.

My next article will shift the focus from making code look better, to the ways in which we can use SQL Prompt to improve the quality of the code, such as by making sure all column references are qualified to the parent object.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more