• SQL Prompt
• SQL Formatting and Styles

Controlling how SQL Prompt Formats your Code: The Knobs and Dials

SQL ignores spaces, tabs and line breaks. It even ignores case, unless you perversely insist that it shouldn’t. Therefore, unlike with Python, you are free to decorate your SQL code as you wish, in order to make it more intelligible to other humans. It doesn’t alter the way it runs.

Herein lies a problem: although the general principles of SQL formatting are fairly set, and agreed, there are many differences in how you manage the detail. The debates around, say, the handling of commas can get quite heated.

When the first “SQL Beautifiers” were created, you merely pasted the code in, and it made it look a bit prettier. This was fine for publishing code online, but SQL Developers all have their own ways of formatting SQL, so they weren’t particularly happy with the lack of control offered by these beautifiers.

When Redgate created SQL Prompt, it put in copious formatting options to let you could fine-tune the way it did it, to your own tastes. There really doesn’t seem a limit to the variety and choice in the way that you format SQL Code, and the only way to understand what’s possible is to understand what each of the knobs and dials do. It’s a useful exercise to go through them all, and I understand the formatting side of SQL Prompt rather better for having written this article.

There are a hundred and fifty-five configurable options, grouped into various sections and I’ll document all the sections in detail, describing the options in each, and what they do. The resulting SQL Prompt Formatting Wallchart is available as a PDF at the bottom of this article.

The underlying concepts

SQL Prompt uses styles, each style being defined by an XML file, stored in a folder. A style defines a set of values for each of the 155 formatting options that govern how to lay out your SQL code. Each option maps to a configuration item in the XML file. Fortunately, Prompt’s user interface makes all this invisible to all but the most intrepid developer. You need never see the XML file.

When you select a style (the “active style”) in SQL Prompt, it loads its XML file, and uses it defines for all subsequent formatting operations (Ctrl K, Ctrl Y).

Each developer can define their personal style and can also load and use shared styles. These styles are generally associated with projects and development teams and are liable to change over time, both as Prompt improves the options and the team refine their style preferences. Therefore, they can be usefully stored in source control so that it is possible to see how it develops over time, and who altered what.

The formatting options

From the SQL Prompt menu, select Edit formatting styles, and you can edit an existing style, or create a new one using select + Create a style…. Give your style a name and specify the style on which it will be based; I like to start from the ‘minimalist’ built-in Redgate style called Collapsed.

The UI lays out all the various groups of options, and you simply click on each one to set the values for each of the individual options it defines. Here are some of the various options that control the formatting of whitespace. You can and should examine the impact of changes, frequently, on a suitable “preview” query.

As far as recommending settings for some of these configuration items, I would like to be rather cagey, but my own XML file is attached to this article, with all the settings I use.

When you are deciding on the values to choose, the most important principle is to treat with respect that SQL is designed to be as close as possible to real English language. It was intended to be written as one would write a sentence. Sure, a long sentence would be broken into phrases and bulleted lists, or poetic lines. If I’m doing this by hand without the help of SQL Prompt, I’ll break the line at the start of a subclause if the query I’m writing is longer than, say, eighty characters long, as I’ve specified above in the ‘Wrapping’ section. I like to indent subordinate clauses and subqueries with just enough spaces to make the point. An ON phrase, for example, would have no existence outside a JOIN phrase, and a JOIN phrase would have no separate existence beyond its FROM clause. The indentation should be obvious.

In deciding on your formatting style, it is wort remembering that formatting is done to emphasize the structure of a query and make it easier to read and understand. It doesn’t have to look pretty. I really dislike SQL where everything is made to line up, like the façade of a Georgian house. I don’t see the point of enforcing vertical lists, large areas of whitespace and lines that start with commas, but I try to avoid being opinionated because such discussions have caused raised voices and flushed faces amongst database developers and DBAs.

Global

Conceptually, the Global formatting options are the ones that require most thought. They deal with the general principles that apply to all formatting, whatever the type or purpose of the code.

Here, you specify how whitespace is handled, how lists are laid out, how parentheses are managed and how the capitalization or casing of keywords, functions and types is done. A minor change here can make a big difference.

WhitespaceListsParentheses

Casing

Statements

The formatting options in the Statements section control the layout of specific structures within SQL DDL, DML, CTE expressions, Statements, control-of-flow statements and variables.

Data

Schema

Control Flow

CTE

Variables

Clauses

How you deal with the subclauses of a FROM clause within statements such as JOIN or ON, and the way that INSERT column lists and values are managed.

Join

Insert

Expressions

How case statements, operators, function calls and VALUES expressions are dealt with.

Function Calls

Case

In

Values

Operators

Conclusions

I’ve found it futile to try to get database developers to agree on a ‘best practice’ for formatting SQL. This is surprising, given the general consensus in other computer languages, but passions are soon raised. SQL Prompt avoids all this by making the format of SQL as configurable as possible and the Prompt development team continue to add ways of configuring the process.

I would suggest that if a team is doing database development, it should settle on the general principles of the formatting style they use, as early as possible, and save the format in source control.

When I’m working in a team, and I feel offended at the thought that my careful formatting is likely to be trashed by a colleague, I always have the option to turn off the formatting by enclosing my code in

…at the beginning and…

…at the end.

References

• SQL Prompt as a Layout Tool: A Survival Guide – My guide to taming SQL Prompt code layout options and getting it to format code exactly the way you like it.
• Laying out SQL Code – My own take on the general topic of laying out code.
• Transact-SQL Formatting Standards (Coding Styles) – Rob Sheldon’s popular and thorough description of all the issues you need to cover when deciding on the way that SQL code should be laid out
• SQL Code Layout and Beautification – William Brewer’s sensible take on the subject, from the perspective of a programmer.
• ISO/IEC 11179 – The international standard for a vocabulary and naming conventions for IT data.
• Joe Celko’s SQL Programming Style – The first book to tackle the subject in depth, and still well-worth reading. You may not agree with all he says but it will still improve your SQL Coding, as it is packed with good advice.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more