Product articles SQL Prompt SQL Formatting and Styles
Controlling how SQL Prompt Formats your…

28 November 2019

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

28 November 2019

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

Phil Factor explores and discusses the current state of the art in SQL Formatting, as done automatically by SQL Prompt.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

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

-- SQL Prompt formatting off

…at the beginning and…

-- SQL Prompt formatting on

…at the end.

References

Downloads

Share this post.

You may also like

  • Article

    Quick SQL Prompt tip – script objects as ALTER in two clicks

    Working in a large database can be difficult at times. While many of us might learn the meanings and definitions of most objects, it’s easy to forget the exact ways in which some objects work, or what the behavior is in certain calls. This is one place where having tools that assist you like SQL

  • Article

    Insert Statement Without Column List (BP004)

    Many production databases have failed embarrassingly as a result of INSERT code that omits a column list, usually in mysterious ways and often without generating errors. Phil Factor demonstrates the problem, and advocates a 'defense-in-depth' approach to writing SQL, in order to avoid it.

  • Article

    How we used your feedback to improve parentheses formatting in SQL Prompt 8.1

    After we released SQL Prompt 8, our priority was to continue to support the new formatting engine by fixing any bugs and reviewing feedback from users. One of the recurring patterns we noticed in your feedback regarded parentheses formatting. Some of you told us you couldn’t format the parentheses exactly the way you wanted to.

  • Article

    Misuse of the scalar user-defined function as a constant (PE017)

    The incorrect use of a scalar UDF as a global database constant is a major performance problem and should be investigated whenever SQL Prompt spots this in any production code. Unless you need to use these global constants in computed columns or constraints, it is generally safer and more convenient to store the value in an inline table-valued function, or to use a view.