• SQL Prompt
• SQL Formatting and Styles

The Louis Davidson custom style for SQL Prompt

My previous article in this series explained why it’s important for a development team to adopt a common standard for formatting SQL code. It also gave a broad overview of the styles and actions within SQL Prompt that can help define and implement that standard. Unfortunately, many teams avoid the topic of a standard code formatting style as it is too much work to make it happen.

The biggest problem is deciding what the team formatting style for SQL should look like. The second problem is that, having endured the long and contentious process of agreeing on a standard style, there’s then no guarantee that everyone in the team will apply it consistently! SQL Prompt can help make this process a bit easier:

1. It offers a good starting point – since it ships with several pre-defined SQL code styles.
2. It allows you to create custom styles – you can start with the pre-defined style that most closely matches the team’s preferences and requirements and then adapt any number of style formatting options to create your team’s own custom style.
3. You can easily share the custom style with the team– simply pop it in a shared folder and have every team member access it from there
4. Applying the style, or switching between styles, is easy – simply set the style to Active style and run the Format SQL command.

Choosing your standard

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 the SQL for a few common tasks, all using the downloadable WideWorldImporters sample database. It includes a query that will return the 12 states in the US that have a city named Nashville, along with the code to create a temp table and a stored procedure.

The code will execute just fine, 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.
• Number of lines between statements – should there be a line between statements? Or should statements be one on top of another.

I could go on, 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, functions, CTEs, and so on, and your team members could find a million formatting variations for each piece of code.

Listing 2 offers this writer’s opinion on how the query should be formatted, when formatting using my own ten fingers:

Listing 2

Over the coming sections, I’ll demonstrate how to use SQL Prompt to create a custom style that matches as closely as possible how I have formatted SQL code, manually, for years. It’s not an exact match though; I’ve had to make some allowances for what the tool can do. If you’re going to use SQL Prompt to implement your team standard, then you must consider that it gets a non-vetoable vote in how the code looks. It offers a lot, but not limitless flexibility in how the code can be formatted. If the standard you want to apply is rather atypical, it may not support it.

Starting from a clean slate

I started out by resetting all SQL Prompt settings back to their defaults, which you can do by navigating SQL Prompt | Options and hitting Restore all defaults in the bottom left of the screen.

In defining my SQL code formatting style, in this article, I’m limiting myself to customizing only the style option settings (ie, those you see when you edit an existing style).

To make the code better, as opposed to just better looking, I’d also consider customizing some of the options that SQL Prompt considers refactoring options, such as imposing proper object qualification and aliasing on all column names.

Some of those ‘refactoring’ options will run as part of the Format SQL command, so if you tweaked those settings from the default, then your actual output may look slightly different than mine, after running the command. As I noted in my previous article, it’s possible to export and import the Prompt.settings file, which includes all settings except the style option settings.

Defining the Louis Davidson custom style

Open SQL Prompt’s Formatting styles dialog (from the SQL Prompt menu in SSMS) and, with the Redgate Default style selected, select Copy… from the vertical ellipsis and enter a new style name, in my case LouisDavidsonStyle.

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. Figure 1 shows the Formatting styles screen for the new style:

Figure 1

SQL Prompt allows us to set all sorts of preferences for how our statements, WHERE clauses and JOIN conditions are formatted, generally, as well as how particular database objects are formatted, such as functions or CTEs

I’ll walk through each set of options and indicate where I’ve chosen to adapt the default settings and include the rationale. Note that some of the options I discuss are available only in SQL Prompt v8, or via the ‘experimental features’ section of SQL Prompt v7, but not in earlier versions.

---

Global options

Whitespace

In the New Lines section:

• Uncheck Preserve existing empty lines between statements – I want to specify to add one line between all statements, as well as after GO.
• Check Preserve existing empty lines within statements

Lists

Under List Items:

• Change the values of Place subsequent items on new lines from Always to If longer than wrap column – usually, I prefer to see the column list in a compact manner. Often queries can have 10s or 100s of columns and that can make the code far too large.
• Check Align items across clauses – this makes SQL look a lot cleaner. My personal style is to only have clauses on the left side of the query, so things line up and look very tidy.

Parentheses

Under Parentheses:

• Uncheck the Place opening parenthesis on new line – this usually looks better.
• Uncheck the Place closing parenthesis on new line – for most parenthesis usage, it is better not to place the final parenthesis on a new line.

Under Parenthesis Contents

• Change the value of Place on a new line to If longer than wrap column – keeping the parenthesis contents tight and on one line is generally a cleaner look.
• In Spaces, uncheck Add spaces around parentheses – I don’t like to see extra spaces anywhere, if possible.

Casing

Note that these casing options will also be applied automatically as you typing new SQL code, as well as when formatting existing code.

• Change Built-in data types to lowercase – while I like to see SQL statements, system functions, and global variables in uppercase, datatypes always seem better in lowercase to me.
• Change Global variables to uppercase – since I generally abhor any ‘all caps’ in my code, it makes system code ‘pop put’.

---

Statements options

Data (DML)

Under Subqueries:

• Uncheck Place parenthesized FROM sources on new line – I prefer my derived tables to show up as a table would, right on the same line as the FROM or JOIN.
• Uncheck Collapse subqueries shorter than x characters – I can see why this makes sense for very short subqueries, but I generally want to see subqueries formatted just like regular queries.

Schema (DDL)

Under Parenthesis:

• Uncheck Use global parentheses options for CREATE/ALTER statements – I like parenthesis formatting to look distinctly different from DDL that for queries.

• Make sure both Place opening parenthesis on new line and Place closing parenthesis on new line are checked and have To statement alignment.

• Choose Indent parenthesis contents – this is an absolute must in my book, otherwise it looks very awkward!

Under Columns and Parameters:

• Choose Align data types and constraints – This is something I never do when I am formatting coding manually. However, when the tool does it, you don’t have to mess about every time there is a minor change.

Finally, for Constraints:

• Uncheck Place constraints on new lines – constraints should go on the same line as the declaration, if they fit in the width. Leave Place constraint columns on new lines, set to If longer or multiple columns.

Variables

Under Declare:

• Check Align data types and values – just like in DML, it is nice to have things aligned in DDL, and doing it manually would be very tedious.

---

Clauses

Join

Under Join:

• Set Join keyword alignment to To Table – This is one of the places where I can’t use my personal standard.

I usually indent two spaces from the table, such as:

But this SQL Prompt option setting only allows:

Big deal? Nope, but it is something that I notice when I see it.

---

Expressions

Function calls

Under Arguments:

• Remove Add spaces around parenthesis – I don’t like to add extra spaces

CASE

Under WHEN:

• Change Place first WHEN on new line to If there is an input expression – when the CASE expression uses the simple syntax (CASE value WHEN value THEN …), then place the WHEN on a new line, otherwise do not.
• Change WHEN alignment to To first item – aligning to items generally looks best in most cases.

For THEN expressions:

• Uncheck Place expression on new line.
• Check Place THEN on new line. – the expression and the THEN are more natural when they are on the same line.

Operators

Under AND/OR:

• Change Alignment to Indented from statement– this indents expressions enough so they are clearly distinguished from clauses of the statement.

Under BETWEEN:

• Uncheck Place BETWEEN keyword on new line – The BETWEEN operator is just like any other comparison operator, and should be on the same line with the comparison values.

---

Applying the custom style

If the preview of the custom style looks good, Save your work and close the Formatting styles dialog.

Make sure that your new style is the active one. Then, in SSMS, use Control K, Control Y, or select Format SQL from the SQL Prompt menu to apply formatting. Now the code looks as shown in Listing 3:

Listing 3

Obviously, my sample doesn’t cover every possibly scenario. I tested it against lots of my corporate code and personal scripts. I suggest you do the same with yours and see how it works for you.

Sharing the custom style

Once you have defined your custom style (or downloaded mine), you can share this style with your team members, as I described in my previous article.

Locate the .sqlpromptstylev2 file that you created, by fetching it from the SQL Prompt style folder, which is located (by default) here: \%LOCALAPPDATA%\Red Gate\SQL Prompt 8\StylesV2.

Copy it to a shared folder to which the whole team have access. Each member of the team changes their style folder to the shared folder. The alternative is simply to send the style file to your coworkers and they can apply the file in their directory.

I’ve started to place all my custom styles in a Dropbox folder, just so I have access to them from any machine on which I have a SQL Prompt license!

Summary

I hope this article demonstrated how SQL Prompt can help you standardize the SQL formatting style of your team, and make it easier to implement. It will give you a lot of control over how code looks, and most typical, professional coding standards will be supported well enough to make them your companies standard.

I have shown you several changes I would make to the default SQL Prompt style, and why, but it will be up to you and your team members to decide exactly what you want your code to look like.

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.

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.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more