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).
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.
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.
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!).
The Apply casing options and Insert semicolons actions, as well as the next three on the list, Expand Wildcards, Qualify 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.
One of the greatest conveniences of the SQL language being able to type
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.
SELECT * FROM [Application].[Cities] [City];
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.
SELECT City.CityID, City.CityName, City.StateProvinceID, City.Location, City.LatestRecordedPopulation, City.LastEditedBy, City.ValidFrom, City.ValidTo FROM [Application].[Cities] [City];
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.
SELECT CityID, CityName, StateProvinceName FROM [Application].[Cities] [City] JOIN Application.STATEPROVINCES ON STATEPROVINCES.StateProvinceID = City.StateProvinceID;
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.
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).
SELECT City.CityID, City.CityName, StateProvinceName FROM Application.Cities [City] JOIN Application.StateProvinces ON StateProvinces.StateProvinceID = City.StateProvinceID;
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.
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
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.
SELECT [City].[CityID], [City].[CityName] AS [City Name], [StateProvinces].[StateProvinceName] AS [State Name] FROM [Application].[Cities] [City] JOIN [Application].[StateProvinces] ON [StateProvinces].[StateProvinceID] = [City].[StateProvinceID];
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.
SELECT City.CityID, City.CityName AS [City Name], StateProvinces.StateProvinceName AS [State Name] FROM Application.Cities City JOIN Application.StateProvinces ON StateProvinces.StateProvinceID = City.StateProvinceID;
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.
SELECT City.CityID AS CityId, City.CityName AS [City Name], City.StateProvinceID FROM Application.Cities City;
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.
SELECT City.CityID AS CityId, City.CityName AS [City Name], City.StateProvinceID FROM Application.Cities AS City;
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.
SELECT City.CityID, City.CityName AS [City Name], StateProvinces.StateProvinceName AS [State Name] FROM Application.Cities City JOIN (SELECT * FROM Application.StateProvinces) StateProvinces ON StateProvinces.StateProvinceID = City.StateProvinceID;
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).
SELECT City.CityID, City.CityName AS [City Name], StateProvinces.StateProvinceName AS [State Name] FROM Application.Cities AS City JOIN( SELECT StateProvinces.StateProvinceID, … StateProvinces.ValidTo FROM Application.StateProvinces) AS StateProvinces ON StateProvinces.StateProvinceID = City.StateProvinceID;
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.
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
[City Name] aliases.
SELECT City.CityID CityId, City.CityName [City Name], City.StateProvinceID FROM Application.Cities AS City;
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.
SELECT CityId = City.CityID, [City Name] = City.CityName, City.StateProvinceID FROM Application.Cities AS City;
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!
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.