3 August 2017
3 August 2017

Using multiple custom SQL formatting styles in SQL Prompt

Guest post

This is a guest post from Louis Davidson. Louis is a senior data architect for the Christian Broadcasting Network in Virginia Beach. A highly experienced Microsoft SQL Server MVP, he speaks at most PASS conferences and a large number of SQL Saturday events.

He regularly contributes articles to both his own blog and Simple Talk, and has written a book on database design for each of the last five major releases of Microsoft SQL Server.

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.

This article shows how to use SQL Prompt formatting styles to create and maintain multiple code styles, each for a dedicated purpose, and to switch between them and apply a new style to existing code, with ease.

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.

SQL formatting 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.

SQL formatting 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.


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.

Guest post

This is a guest post from Louis Davidson. Louis is a senior data architect for the Christian Broadcasting Network in Virginia Beach. A highly experienced Microsoft SQL Server MVP, he speaks at most PASS conferences and a large number of SQL Saturday events.

He regularly contributes articles to both his own blog and Simple Talk, and has written a book on database design for each of the last five major releases of Microsoft SQL Server.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    SQL Prompt Hidden Gem: Auto-fill the GROUP BY clause

    “Have you seen this new feature that auto-fills the GROUP BY with non-aggregated columns!” exclaimed my co-worker, soon after she had installed a new version of SSMS. I hadn’t, but I was intrigued. I must have written thousands of aggregate queries in my career, so it sounded like it could save me some time. I

  • Article

    Customizing the SQL Prompt built-in snippets: a better ALTER TABLE ADD (ata) snippet

    Snippets are a great feature of SQL Prompt. They save coding time, and introduce standards and consistency to the way you build code modules. They have multiple replacement points (placeholders) for parameters, and you can invoke them directly from an SSMS query pane. SQL Prompt also comes with many useful built-in snippets, but sometimes we

  • Article

    SQL Prompt Code Analysis: Avoid using the ISNUMERIC Function (E1029)

    Avoid using the IsNumeric() function, because it can often lead to data type conversion errors, when importing data. SQL Prompt Code Analysis rules include an Execution rule, E1029, which will alert you to use of this function, in your T-SQL. If you’re working on SQL Server 2012 or later, it’s much better to use the Try_Convert()or

  • Article

    SQL Prompt Code Analysis: INSERT INTO a permanent table with ORDER BY (PE020)

    The SQL query that is used to produce the result that is inserted into the permanent table has its order specified by an ORDER BY statement. Relational tables are not ordered, so the ORDER BY is meaningless. Use a Row_Number() window clause instead, if you need to impose a particular order on rows in the

  • University

    Take the SQL Prompt course

    This Redgate University course takes you from installation all the way up to getting the most out of the advanced operations in SQL Prompt. As well as autocompleting your code, you’ll learn how SQL Prompt can help you with code formatting, object renaming, code analysis, and other useful tip and tricks.

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly