• SQL Prompt
• SQL Refactoring

Beyond Formatting: Improving SQL Code using SQL Prompt Actions

When writing SQL code, there are a few simple, but good, habits you can adopt to help improve the overall quality of your SQL code, in subtle but meaningful ways. For example,

• Qualifying SQL Server object names – by qualifying an object to its schema, you remove ambiguity and promote execution plan reuse.
• Avoiding use of select * – this will prevent unnecessary network load, query performance issues, and avoid problems caused by the column order changing, when inserting into a table.
• Removing unnecessary square brackets from SQL scripts – will make them much easier to read
• Standardizing the use of aliases – promotes consistency and readability and so makes code much easier to review

Fortunately, SQL Prompt actions helps each developer enforce these simple best practices or SQL Code with a few mouse clicks.

SQL Formatting versus SQL Refactoring

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.

• SQL Formatting changes are purely ‘decorative’; they affect only whitespace. We lay out all the clauses, expressions, lists and parentheses 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).
• SQL Refactoring ‘lightweight’ refactoring changes that do alter the text of the query, though without affecting the meaning or intent of the code.

This article is about the ‘lightweight’ refactoring actions. SQL Prompt can also perform ‘heavyweight’ refactoring processes that require a separate window or wizard to accomplish , such as renaming objects or splitting tables, or encapsulating code as a stored procedure. See my articles, Refactoring a SQL Batch or Object with SQL Prompt and Database Refactoring with SQL Prompt for more on these topics.

The SQL Formatting 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

Y0u specify which actions you wish to run automatically, as part of Format SQL, using the Actions sections of the Format | Styles area of the SQL Prompt – Options dialog. As you can see from Figure 3, four actions are set to run by default, although of those four only Prefix N to unicode literal string variable assignments and Insert semicolons are strictly refactoring actions, and there are five more actions that you can enable if you wish (and I use all of them!).

Figure 3

The Insert semicolons action, as well as Expand Wildcards, Qualify Object names, Add/Remove square brackets and Apply casing options can run independently, as described previously. The 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.

The final two actions on the list relate 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 and I won’t cover them here.

• 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.

It’s time to see these refactoring actions…in action. 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: Avoid use of SELECT *

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. SQL Prompt has a built-in code analysis rule that alerts you to use of SELECT * in your code. See The Sins of SELECT * for more on this.

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 SQL 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 was 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).

Removing unnecessary 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.

For more, see Phil Factor’s festive take on square brackets: Removing the Square Bracket Decorations with SQL Prompt

Using a standard format for SQL 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.

Summary

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.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more