Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Beyond Formatting: Improving SQL Code Using SQL Prompt Actions

DZone's Guide to

Beyond Formatting: Improving SQL Code Using SQL Prompt Actions

Consistent formatting, aliasing, and name qualifying just make your code look great. Learn how to do this with SQL Prompt's refactoring actions.

· Database Zone ·
Free Resource

Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

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 vs. Refactoring in SQL Prompt

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

  1. 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).

  2. Refactoring changes alter the text of the query 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, 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 these changes 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 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 preferences 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 the 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 is 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 is loaded with an average of 2MB of data per row. The next time that the 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];

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.

SELECT City.CityID,
         City.CityName,
         City.StateProvinceID,
         City.Location,
         City.LatestRecordedPopulation,
         City.LastEditedBy,
         City.ValidFrom,
         City.ValidTo
  FROM   [Application].[Cities] [City];

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 currently 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 two-part (or three-part) names, and column references qualified by their parent object.

Every user has a default schema, which is usually the dbo schema. This means that 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 five 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;

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

SELECT City.CityID,
         City.CityName,
         StateProvinceName
  FROM   Application.Cities [City]
         JOIN Application.StateProvinces
             ON StateProvinces.StateProvinceID = City.StateProvinceID;

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 they 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 the 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];

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, and 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;

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 help 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 it's 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;

Listing 7

Activate the Add/remove AS keyword on alias definition for tables and views setting, 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;

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.

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;

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

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;

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.

SELECT City.CityID CityId,
         City.CityName [City Name],
         City.StateProvinceID
  FROM   Application.Cities AS City;

Listing 11

If we choose to use alias = column, the code will look as shown in Listing 12, once formatted. Note that 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;

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!

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 thatwill be much easier for your team to understand and use.

Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

Topics:
database ,refactoring ,formatting code ,sql prompt ,sql ,tutorial ,format sql

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}