• SQL Prompt
• SQL Formatting and Styles

SQL Prompt as a Layout Tool: A Survival Guide

Here is some SQL code to reveal the highest-performing salespeople at AdventureWorks and listing their managers.

It produces the following result…

So, the code works, but it is ugly. If I need to understand and improve the code, I’ll first need to get it into a readable form.

I have SQL Prompt, so I can hit Ctrl K -Ctrl Y (mnemonic ‘Kick Yourself’), it applies its default built-in code style and that fixes that. Well, no it doesn’t, because I’m confident that you still don’t like the way it is formatted. No two developers can ever agree about how code should be formatted.

After haughtily rejecting all the other built-in styles, (SQL Prompt | Active Style…), you decide to create your own.

Working with custom styles

Once you have created your own style, you can hang onto it. If you look in the line at the top of the Formatting Styles window (SQL Prompt | Active Style | Edit Style or SQL Prompt | Edit Formatting styles), you can see where they are currently stored (\%LOCALAPPDATA%\Red Gate\SQL Prompt 9\StylesV2).

You can see that the code styles are XML files with a whole plethora of settings:

You can store your styles anywhere that can be reached by the filesystem, so you can share your preferred style with your colleagues, making it easier and quicker for them to disagree with it.

You can save the styles in Source Control, which is especially useful if you’re lucky enough to agree on a ‘common style’ for the team. Of course, you can still apply your own preferred style when you need to work on code, then reapply the team style just before committing your changes.

I suggest that you edit your preferred style occasionally, because the Prompt Team are actively increasing the number of options and putting in corrections, and you can get a nice surprise when SQL Prompt suddenly gets closer to your preferences. I wouldn’t advise you to edit the XML directly, though, because the SQL Prompt style editor is so cute. It also lets you see the result of all your adjustments on the current query.

Creating your custom style

I like to get as close as possible to the styles used by Itzik Ben-Gan in his books, which is also close to what Joe Celko uses in his classic books. I will argue with anyone that SQL was always intended to be as close to real written language as possible. It was designed so anyone could understand it. Its phrases, clauses and punctuation should be as close as possible to what you see in literature. It is a great mistake to spread it across the page like a table with strange alignments, or commas down the left margin.

If you are struggling against these sorts of heresy in the corporate code style, then simply use ‘kick yourself'(Ctrl K-Ctrl Y) to get it to the intelligible style you like best, and then reapply the corporate style just before you save it to source control

Start from a minimal base

You create a new style from the Formatting Styles window, and its base will be an existing style. Call it what you will, but I’ve chosen ‘Better’ and based it on built-in Collapsed style, just because it doesn’t do much formatting, so there is less to undo.

This launches you into the Prompt style editor, and you need to work out how to edit all the styling options, and actions, to get as close as possible to your preferred layout.

One significant change I make is to enable wrapping, and set “the wrap” to 78 characters, since I publish a lot of code online. I then go through and make sure the various clauses, expressions and variables line up as I like, on the new line, when wrapping occurs.

I won’t walk through every detail of all the other style options I tweak, but for every option, I simply ask myself “What would Celko or Itzik choose?“. I’ve provided my custom ‘better’ style as a download, and you can look through the various settings, and edit them as required. You could also check out Louis Davidson’s custom style, in case that’s closer to your preferred style.

Preview the changes frequently

As you try out different settings you should review the impact of each change, either on the sample code that Prompt provides, or on your own query.  The sample code is well-chosen to show you the full impact of your style choices, but with your own query, you can really see how it deals with your requirements. All you need to do is by check the Preview current query box at the bottom.

As you go through the various options, you will shake your head in amazement that anyone would wish to style their code in that way, but every option you see has been requested at some point, some of them by me.

Search the options

If you can’t find the option that you need, there is a search option that allows you to find it. While Prompt provides several options for putting various clauses and expression onto new lines if the line exceeds the wrap, I wanted one that dealt with the AS phrase for a derived table, like the SalesPerformance one in our example. With the search option, I was able to confirm that there is an option for putting the AS on a new line in CTEs, but not one for derived tables. I bet it will be there soon!

Finishing touches

With a few minutes of effort, I was there. You’ll see that I got around SQL Prompt’s desire to make a longer line than the wrap, for the derived table, by putting in an inline comment followed by a carriage return, at the point where I wanted the line break.

If you’re developing new code, it’s best to get the code formatted as you want, before adding all the inline comments required to explain what it’s supposed to do. Inline comments can restrict things a bit for Prompt, so it is sometimes better to do it after the code is reasonably well laid out.

Selectively disabling formatting

If SQL Prompt can’t cope with a section of code, even with the help of your new custom style, then you can simply highlight the lines you don’t want Prompt to format, and use the “Action list” to select the Disable formatting for selected text action.

All this does is embed two inline comments around the selected text, so you can also do it just by adding these comments manually:

Many SQL Prompt users will have code that has these comments around it, telling Prompt not to attempt formatting it. This is often because Prompt can make a mess of it. Currently, for example, it can do very strange things to Extended Events CREATE EVENT SESSION code). You need to review that occasionally by removing the comments and trying again, because the developers for SQL Prompt spend a lot of time reducing those quirks.

Unformatting your code

Occasionally, I find it useful to be able to unformat the SQL code entirely, by highlighting the code and choosing the Unformat action. This will undo the effects of all layout options (those that affect whitespace only).

It will remove all line beaks, except where there are inline comments, as otherwise the inline symbol (--) will comment out the code.

However, unformat will not undo the effects of any actions you’ve applied that change the text of the code.

Changing your code with actions

Most of the options that are part of a Prompt code style affect whitespace only, and there is also a set of casing options for keywords, built-in functions and so on. The options that only affect whitespace are classified as layout options. When you hit Format SQL (‘kick yourself’) SQL Prompt applies the layout options defined by your custom style, by default, by running the Apply layout (format SQL) action.

It will also, by default, automatically run the Apply casing options, Insert semicolons and Prefix N to Unicode literal string variable assignments actions:

These actions, and others, will change your code, i.e. they alter the text of the queries. Prompt will go further in this direction should you wish, allowing us to enable the following actions to also run as part of Format SQL:

• Expand wildcards
• Qualify object names
• Apply a column style alias
• Add or remove square brackets
• Add or remove AS keyword on alias definition for tables and views

You can’t reverse out of any actions that change the text, using Unformat, so Ctrl-Z is your best friend here. However, this is a slightly limited rescue because you also must reverse everything done since you made the foolish decision to add square brackets everywhere. Thankfully, there is usually an action that is opposite to it, such as Remove Square Brackets and its evil twin Add brackets to all identifiers.

You might think that most of these actions are harmless, and they generally are, but they can be irritating. If you decide, for example, to format code, automatically allowing Prompt to Insert semicolons, while you are still developing it actively, then you will either have to take them out again when you add a clause or remember to insert the clause before the semicolon. If something irritates you like this, then disable this action and run it manually when you’ve finished.

Having the Expand Wildcards action run automatically sounds useful, but sometimes, as when using OpenJSON with a WITH clause to provide explicit schema, the wildcard is more appropriate than a long redundant list of columns that might eventually contradict the explicit schema if you alter it.

Qualifying object names just make no sense if there is only one table source. It just makes plain simple code longer.

In short, I suggest that some of these actions should only be performed separately to the layout. This is very easy to adjust. You just use Prompt’s Options dialog to select the actions that you don’t want performed as part of Format SQL:

You will see that I don’t run Expand wildcards or Qualify object names as part of Format SQL, because I prefer to do that separately from the layout. I’ve chosen to keep the Insert semicolons action running by default because, despite occasional annoyances, I so often forget to do it later. For the others, I can just run these actions individually, as required,  from main Prompt menu or from the Action list, or using a keyboard shortcut.

Remember that Prompt Options are applied universally, regardless of what custom style you are using. If at any point you need to reset these options to their defaults, you can hit Restore all defaults in the bottom left of the Prompt Options screen or revert just one set of options (such as Suggestions > Join conditions) to their defaults using the top right Restore defaults one.

Conclusions

The layout options of SQL Prompt have always been handy. They came to SQL Prompt when two Redgate tools, SQL Prompt and SQL Refactor, were combined. After that, it lay quietly until it was completely revised to version 2. This revised layout tool was so much better, in that it could mimic the layout styles used in published and edited SQL code. This suddenly promoted layout into one of the major reasons for using SQL Prompt.

The very fact that there is so much disagreement about the correct layout for SQL Server makes it an extraordinarily difficult to provide a tool that accommodates all tastes. SQL Prompt has, by dogged incremental improvements over several years, succeeded well with this.

There was a time when grey-muzzle SQL Developers had their own routines to format code. It was fun. It only had to format the code the way we wanted it formatted, and it exercised skills in recursive descent and other parsing techniques that one picked up in college. With SQL Prompt, all this is unnecessary. It now does layout well enough for almost all requirements. I must now admit that I use it for most of the articles I write.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more