14 March 2018
14 March 2018

Beyond Formatting: Improving SQL Code using SQL Prompt Actions

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.

In this article, I’ll discuss how I use the SQL Prompt actions that you can apply as part of the Format SQL command. These actions are designed to help improve the overall quality of your SQL code, in various subtle but meaningful ways, such as qualifying object names, standardizing the use of aliases, adding semicolons to the end of statements, and removing unnecessary square brackets around object names. Although these actions can run as part of Format SQL, they extend beyond the scope of ‘formatting’, since they change the actual text of the query, without changing the meaning or intent of the query.

Formatting versus refactoring in SQL Prompt

When laying out code using SQL Prompt, by running the Format SQL command, we can apply two distinct types of changes to the code: formatting and refactoring.

Formatting changes are purely ‘decorative’; they affect only whitespace. We lay out all the clauses, expressions, lists, parentheses, and so on, in our code by adding or removing carriage returns, linefeeds, tabs and spaces. If we were to remove all formatting from a piece of code, it would continue to function exactly as before. We save our preferred formatting options as a code formatting style. Having set that style as “active”, we apply those style options to the selected code using Format SQL (Ctrl K. Y).

Learn how to use Prompt’s custom formatting styles

My previous articles showing how to use SQL Prompt’s formatting styles to implement a SQL coding formatting standard, offered the ‘Louis Davidson’ take on what sensible SQL formatting standard looks like, and explained how developers could use multiple custom styles, alongside the agreed team standard.

Refactoring changes alter the text of the query, though without affecting the meaning of the code. SQL Prompt offers several different categories of refactoring for SQL objects and code. It can perform ‘heavyweight’ refactoring processes, such as renaming objects or splitting tables, or encapsulating code as a stored procedure. These require a separate window or wizard to accomplish and won’t be discussed in this article.

This article is about the ‘lightweight’ refactoring actions. These make less-intrusive changes, but which do alter the query text.

Running the actions

I’m going to limit the scope of this article to the refactoring actions that can run as part of the Format SQL command. Of these actions, five of them can also be executed individually, independently of Format SQL, by selecting them from the SQL Prompt menu.

Figure 1

There are additional actions (such as adding or removing comments) that can only be run independently, which I will cover in a future article. To apply any individual action that can run, or only runs, independently of Format SQL, simply highlight the relevant text in a query pane, and select the action from the Actions list, as shown in Figure 2.

Figure 2

Configuring the Format SQL Actions

In the Actions sections of the Format | Styles area of the SQL Prompt – Options dialog, we configure which actions we wish to run automatically, as part of Format SQL.

As you can see from Figure 3, three actions are set to run by default, although of those three only Insert semicolons is strictly a refactoring action, and there are five more actions that we can enable if we wish (and I use all of them!).

Figure 3

The Apply casing options and Insert semicolons actions, as well as the next three on the list, Expand WildcardsQualify Object names, and Add/Remove square brackets can run independently, as described previously.

The final two actions relating to aliases, are available only by running them as part of Format SQL, and you must use the Prompt Options menu to set your references for how you want aliases to be formatted.

Using the Format SQL actions

It’s time to see these refactoring actions…in action. In my personal environment, I enable all these actions, since I feel it produces standard, well-formatted code that is easier to read.

The first two actions on the list related to application of the ‘formatting’ style options. They are there to allow us flexibility in applying the layout and casing options defined by the current active formatting style.

  • Apply Layout –deselect this to prevent Format SQL from applying any of the formatting styles related to code layout (whitespace). It will still apply any other selected actions.
  • Apply casing options – deselect this to prevent Format SQL from applying casing options to keywords, built-in functions, datatypes and variables.

I covered the formatting styles in detail in my previous actions, so let’s move on the those that do some refactoring.

To start this exercise, I used the Restore all defaults button, at the bottom-left of the SQL Prompt – Options dialog, to revert to the defaults for all settings, except for the formatting styles that I have stored in my connected folder and which I share between several machines.

Expand wildcards

One of the greatest conveniences of the SQL language being able to type SELECT * FROM… and get back every column of a table, or of multiple tables through joins. However, while this device is great for exploring a database, it should be avoided as part of compiled SQL code.

Rule 9 of Codd’s 12 Rules describes the principal of logical data independence, and core to this is the idea that the order of the columns doesn’t matter to the design of a table; we can alter a table, drop a column and put it back at the end of the table, and logically this is considered the same table. However, any application that issues a SELECT * query would now receive the columns in a different order, which the calling application would need to handle. This is the least complicated of possible issues, with new and removed columns being much more troublesome.

More practically, consider a million-row table with three integer columns. Each row will consume about 12 bytes of storage, plus some overhead. An application issues a SELECT * query; it runs super-fast. Then, to support some new functionality, a developer adds a varchar(max) column to the table, and it is loaded with an average of 2MB of data per row. The next time that SELECT * query runs, it returns different results, and runs slower.

Of course, the real issue, for developers, is that typing a bunch of columns out is kind of a drag, right? By enabling SQL Prompt’s Expand wildcards action for formatting, this will be done for you. Listing 1 shows a SELECT * query in the WideWorldImporters database.

Listing 1

To demonstrate what the action will do, you can highlight the statement with the wildcard (*) and select Expand wildcards from the action menu (type part of the action name to filter the action list), or simply hit the TAB button. SQL Prompt will replace the wildcard with all the column names from the underlying table.

Listing 2

Notice that it has used our City alias to identify the parent table of each column. In the absence of an alias, it would have qualified the column names with the table name. This is done even if the next setting Qualify object names is not set. You may also note that location is a SQL Server keyword (and hence is colored blue in SSMS by default), but not a reserved word, so it can be used as an identifier. SQL Prompt did not apply square bracket delimiters around the location column name.

If you want to incorporate Expand wildcards into your code formatting standard, simply activate the Expand Wildcards checkbox shown in Figure 3. After hitting OK to close the Options dialog, you can use the Format SQL command to apply the current active style, and expand any wildcards in the query pane, or highlighted code.

While you can format your query while connected to any database, assuming it parses, you must be connected to the parent database of the table, or use a three part name, (such as WideWorldImports.Application.Cities) to expand a wildcard.

Qualify object names

The next great coding practice we will tackle is qualifying object names. Applying this action will ensure that objects are referenced 2-part (or 3-part) names, and column references qualified by their parent object.

Every user has a default schema, which is usually the dbo schema. This means we can refer to a table, dbo.tableName, as simply tableName but there are a few issues with this. Firstly, a two-part object is far less ambiguous to the reader. Secondly, failure to qualify the owner of an object forces the engine to check for the object in two places, first in the authenticated user’s default schema and then in the dbo schema. Also, if the same tableName exists in multiple schemas then a query might reference different tables on different executions, if the default has changed, or if a new table wass created.

In our previous single-table example there was really no need to qualify the parent table but if we add in a JOIN to StateProvinces, it becomes less clear what each column is referring to. Often in a query there will be 30 columns, and 5 tables, and not all of them will have columns with such clear names.

Listing 3

Note also, in Listing 3, the deliberate use of all capitals for the StateProvinces table name to show that the name changes to the correct name. To qualify the table and columns correctly, we can simply highlight the code, and invoke the action.

Figure 4

The resulting query will look as shown in Listing 4. You will note that the square brackets were removed from Application.Cities when it was qualifying the name (even though it technically didn’t need changing).

Listing 4

By default, SQL Prompt only qualifies a column if the parent table has an alias. It does reformat the column name to match exactly how it appears in the database metadata.

If you want columns without aliases to be qualified, which I generally do, you will need to modify the default behavior of Qualify Object names, by activating the Qualify column names with table name setting, in the Inserted code > Qualification section of the SQL Prompt – Options menu.

Figure 5

The first setting, Qualify object names with owner name, is very useful, particularly if you use the default (typically dbo) schema and have a habit of writing FROM tablename, instead of FROM schema.tablename.

Another nice feature is that if someone has written code such as SELECT…FROM Cities;, then SQL Prompt will schema-qualify the table for you, if there is only one Cities table in the database (of course, if you are using SQL Prompt’s IntelliSense features, then when you typed Cities you probably included the schema already).

Add/remove square brackets

Square bracket delimiters appear in our code because it allow us to use identifiers that would otherwise be disallowed. Almost any Unicode string will work for a name, as long as we delimit it with square brackets. Of course, if our naming conventions avoid use of any T-SQL reserved words in identifiers, and avoid flouting Unicode naming conventions, then the need to use square brackets should be rare.

Unfortunately, every code generator (including SSMS) will add square brackets to all identifiers, just in case the identifier needs it, because building the algorithm to determine if an identifier really needs brackets is complicated. Perhaps influenced by these code generators, some developers also like to add these square brackets everywhere. The use of square brackets when they are not actually required is one of the more heinous practices in the history of programming. They make readable code very complex to read and gives the code a very unfriendly appearance. Consider the query in Listing 5.

Listing 5

There are only two legitimate uses of square brackets in this code, and they are the ones around the City Name and State Name aliases. While it is generally a heinous practice to include spaces in names, sometimes it is useful for output, such as when you are using the output to build a spreadsheet or report.

However, all the others are superfluous, so highlight the code, select the Remove square brackets action from the Actions list wave them bye-bye.

Listing 6

To remove these brackets as part of your formatting standard, simply activate Remove unnecessary brackets in the Format | Styles area of the SQL Prompt – Options dialog. One of my favorite things about SQL Prompt is that I can use code generation tools that spit out code with very little, if any, formatting, and littered with square brackets. I paste it in a query window, hit Ctrl (K, Y) and, boom, there is professionally formatted code.

One interesting aspect of the Add/Remove square brackets refactoring actions is that only one will be available to you, as a programmer, depending on the chosen settings. If the Add/Remove square brackets refactoring action is not configured to run as part of Format SQL, which it isn’t by default, then only Remove Square brackets is available as an independent action. This will switch to Add Square brackets if we activate the Add brackets to all identifiers setting (see Figure 3) and this will remain the only available option until either we restore the default settings, or explicitly activate Remove unnecessary square brackets.

Using a standard format for aliases

Adopting a standard approach to aliases for tables and views, as well as their columns, is arguably less important than the other code quality issues we’ve tackled so far with refactoring. That said, standard look and feel helps to differentiate well written code from mediocre code.

The actions relating to aliases can only be run as part of the Format SQL command, so we’ll have to use the settings in the Format | Styles area of the SQL Prompt – Options dialog.

Add/remove AS keyword on alias definition for tables and views

I am a big proponent of using the AS keyword for all aliases and Listing 7 is inconsistent in that its present for the column aliases but missing from the table alias.

Listing 7

Activate the Add/remove AS keyword on alias definition for tables and views setting, and choose the Add AS keyword option, and then run Format SQL. Listing 8 shows the result.

Listing 8

We can also Remove AS keyword though personally I’ve never used this option! Interestingly, while the title of this feature is very long, it doesn’t cover all cases. It also works on derived tables and CTEs. For example, in Listing 9, I’ve changed the StateProvinces reference to a derived table.

Listing 9

With Add AS keyword enabled, SQL Prompt will format the code with the AS in front of StateProvinces (and will expand the * as I have set up earlier).

Listing 10

Apply Column Alias Style

In addition to how tables are aliased, SQL Prompt allows us flexibility over column alias style, via the Apply Column Alias Style action. I use column AS alias for simplicity and standardization.

Figure 6

By default, it uses the column AS alias format, which is the one we see applied in Listing 8 and 10, and that’s the one I prefer to use. However, as you can see, there are other options, even including ‘alias’ = expression, which is deprecated.

In Listing 11, there are several ways you can format the CityId and [City Name] aliases.

Listing 11

If we choose to use alias = column, the code will look as shown in Listing 12, once formatted. Note only the single quoted version of ‘alias’ = column is deprecated.

Listing 12

Standardizing which Actions run as part of Format SQL

Unlike the formatting styles, we can only choose one set of refactoring actions at a time. You can import and export them along with all the other SQL Prompt settings from the SQL Prompt – Options screen, which you can see in Figure 7, but not with the ease of changing formatting styles. This can be an issue when you have a need, for example, to include square brackets in one client’s code, but not in another.

Hence you will generally need to stick with one set of formatting actions. Figure 7 shows the ones I have activated, and you will see it is all of them!

Figure 7

Now, just a click of the mouse (or Ctrl (K, Y)) and the code will look like I spent a lot more time on it than I did! Consistent formatting, aliasing, and name qualifying just make your code look great, even if it is not great code to start with.


In this article, we have moved past simply rearranging the code into pleasing patterns and used SQL Prompt’s refactoring actions to improve the quality of the code, at the same time as applying a standard formatting style.

If you choose to apply the actions I’ve described, you’ll end up with consistent SQL code that abides by the common standard for formatting SQL, and will be much easier for your team to understand and use.

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

    Improving legacy code using SQL Prompt code analysis

    Code analysis is a useful technique for identifying potential issues in your code base, and ensuring that it adheres to your pre-defined standards, so that you can release your software product with more confidence. SQL Prompt 9.2 and later also allows you to view a whole list of code issues detected in a SQL script,

  • Article

    3 Results Grid features SQL Prompt brings to SSMS

    You may not have heard about it yet, but three helpful features were recently added to SQL Prompt that are activated when you right-click on the Results Grid in SQL Server Management Studio (SSMS). 1. Copy as IN clause This feature is available when you have values from a single column selected. You can select

  • Article

    When to use the SELECT…INTO statement (PE003)

    We can use SELECT…INTO in SQL Server to create a new table from a table source. SQL Server uses the attributes of the expressions in the SELECT list to define the structure of the new table. Prior to SQL Server 2005, use of SELECT…INTO in production code was a performance ‘code smell’ because it acquired

  • Article

    How to implement a T-SQL code formatting standard using SQL Prompt

    To what extent should a team attempt to impose a uniform SQL coding format? It can sometimes seem like a futile task. All programmers have a preferred formatting style, to which they profess an unwavering attachment. Check the code of ten programmers, though, and you will likely discover at least 15 different formats, since few

  • 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