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

Using Multiple Custom SQL Formatting Styles in SQL Prompt

DZone's Guide to

Using Multiple Custom SQL Formatting Styles in SQL Prompt

Learn how to use SQL Prompt formatting styles to create and maintain multiple code styles and to switch between them and apply a new style to existing code with ease.

· Database Zone
Free Resource

Find out how Database DevOps helps your team deliver value quicker while keeping your data safe and your organization compliant. Align DevOps for your applications with DevOps for your SQL Server databases to discover the advantages of true Database DevOps, brought to you in partnership with Redgate

Usually, when coding SQL, you will want to go along with the coding style of the team you are working with. In my previous articles on SQL Prompt, I explained why a team of programmers might want to adopt a T-SQL coding standard, how SQL Prompt could make this easier, and then how to use SQL Prompt to define and share a custom SQL Prompt style (the Louis Davidson style), allowing everyone to enforce this excellent standard in their own code, with minimal effort.

There is just one problem with this, of course, and that’s that there are various reasons why each developer may want to stray, temporarily, from the prescribed standard. Perhaps they need a slightly amended style that makes it easier to compare two different versions of some complex code. Perhaps they need a different style for publishing code to their blog or into a Word document or email. Perhaps the agreed "corporate" or "team" style simply makes their eyes water. Similarly, if you are a consultant who works at multiple locations regularly, you may have a style for each customer, and one to format the code as you like it while you are changing it.

This article shows how to use SQL Prompt formatting styles to create and maintain multiple code styles, each for a dedicated purpose, and to switch between them and apply a new style to existing code with ease.

Your Private Development Style

Standards are usually designed in two ways: by a committee and by an individual. Most of the time, the only way you will be truly satisfied with a standard is if you are the one that created it, but the standard code formatting style for a team is more likely to be agreed by a committee.

My previous article defined a sensible “base style” that would hopefully steer the committee in the right direction. However, let’s imagine some awful, parallel universe where your team’s agreed coding style means that T-SQL is styled as shown in Listing 1. To achieve this ghastly layout, I used SQL Prompt to define a classic custom style named Committee_Style. If you want to follow along and see how this was implemented, the style is available to download from here.

SELECT
    StateProvinceName AS StateName
  , Cities.CityName
  , SalesTerritory
  , Cities.LatestRecordedPopulation AS LatestRecordedCityPopulation
FROM
    Application.Cities
JOIN Application.StateProvinces
ON  StateProvinces.StateProvinceID = Cities.StateProvinceID
WHERE
    CityName IN ( 'Nashville' , 'Cleveland' , 'Richmond' , 'Atlanta' ,
                    'Milwaukee' , 'Seattle' , 'Orlando' )
ORDER BY
    Cities.LatestRecordedPopulation DESC;

Listing 1

This code isn’t "wrong" and executes just fine, but glancing even briefly at it hurts my eyes. Adopting a pleasing standard is hard enough, but working with a horrible format is next to impossible, so I’m going to make use of the Louis_BaseStyle when I am coding, and only when I am done will I apply the Committee_Style as necessary.

Switching Between Styles

You can switch easily between the Committee_Style and your preferred style simply by setting the Active Style from the SQL Prompt menu, as shown in Figure 1.

SQL formatting 1

Figure 1

After switching to your preferred development style, in my case Louis_BaseStyle, simply apply the Format SQL command. It will transform the code into something much easier on the eyes, as seen in Listing 2.

SELECT   StateProvinceName AS StateName, Cities.CityName, SalesTerritory,
           Cities.LatestRecordedPopulation AS LatestRecordedCityPopulation
  FROM     Application.Cities
           JOIN Application.StateProvinces
               ON StateProvinces.StateProvinceID = Cities.StateProvinceID
  WHERE    CityName IN ( 'Nashville', 'Cleveland', 'Richmond', 'Atlanta',
                         'Milwaukee', 'Seattle', 'Orlando' )
  ORDER BY Cities.LatestRecordedPopulation DESC;

Listing 2

Of course, ignoring the coding formatting standard because an article told you to may have an adverse effect on your career plans, so remember to switch back to the agreed “committee” style and apply it before checking the code into source control or otherwise making it public.

To do this, simply set the Committee_style as the active style and run Format SQL.

A Style for Comparing Code

When comparing two versions of the same code and looking for meaningful differences, I like the SQL code to be laid out in a slightly different way than normal. I generally prefer code to be fairly tightly formatted, listing as many columns as possible on a line. However, when scanning code for differences, I prefer a much more “spaced out” style with the SELECT keyword and each subsequent column on separate lines.

Also, while I promote judicious use of comments in the code, they make spotting meaningful code changes harder (also, of course, some people overdo code comments!).

In Listing 3, all I’ve done is add some verbose commenting to the code from Listing 2 and re-applied the Louis_BaseStyle.

SELECT   StateProvinceName AS StateName, Cities.CityName, SalesTerritory,
           Cities.LatestRecordedPopulation AS LatestRecordedCityPopulation
  --This is a lot of comments
  --The writer had good intentions,
  FROM     Application.Cities --but inner joins just didn't make sense to the writer
           JOIN Application.StateProvinces
               /* Should this really be stateProvinces? Is there really such a thing?
               On Feb 22, 1482, this code was last changed, but I felt like
               writing a book in here would help me understand it*/
               ON StateProvinces.StateProvinceID = Cities.StateProvinceID
  --Originally (not really) this code included an OUTER JOIN. I left this code in 
  --so it would be more clear to me that that is what happened when it was changed
  --OUTER JOIN Application.StateProvinces
  --    /* Should this really be stateProvinces? Is there really such a thing?
  --    On Feb 22, 1482, this code was last changed, but I felt like
  --    writing a book in here would help me understand it*/
  --    ON StateProvinces.StateProvinceID = Cities.StateProvinceID
  WHERE    CityName IN ( 'Nashville', /* Nashville serves great chicken */
                         'Cleveland', /* Wonder if he meant Ohio?*/ 'Richmond',
                         'Atlanta', 'Milwaukee', 'Seattle', 'Orlando' )
  ORDER BY 
        Cities.LatestRecordedPopulation DESC;

Listing 3

Set the Style Options

Let’s create another custom style called Code_Comparison, copied from the Louis_BaseStyle previously referenced.

We’ll make a couple of changes so that columns listed in the SELECT statement and as part of the IN expression are each on a single line:

  • Global | Lists | List items
    • Place first item on new line: Always
    • Place subsequent items on new lines: Always
  • Expressions | IN | Values
    • Place first value on new line: Always
    • Place subsequent values on new lines: Always
  • Statements | Data (DML) | List Items
    • Place GROUP BY/ORDER BY expression on new line: Always
    • Place subsequent values on new lines: Always

Save the new custom style. Listing 4 shows how our code will look in the new style.

SELECT
           StateProvinceName AS StateName,
           Cities.CityName,
           SalesTerritory,
           Cities.LatestRecordedPopulation AS LatestRecordedCityPopulation
  --This is a lot of comments
  --The writer had good intentions,
  FROM     Application.Cities --but inner joins just didn't make sense to the writer
           JOIN Application.StateProvinces
               /* Should this really be stateProvinces? Is there really such a thing?
               On Feb 22, 1482, this code was last changed, but I felt like
               writing a book in here would help me understand it*/
               ON StateProvinces.StateProvinceID = Cities.StateProvinceID
  --Originally (not really) this code included an OUTER JOIN. I left this code in 
  --so it would be more clear to me that that is what happened when it was changed
  --OUTER JOIN Application.StateProvinces
  --    /* Should this really be stateProvinces? Is there really such a thing?
  --    On Feb 22, 1482, this code was last changed, but I felt like
  --    writing a book in here would help me understand it*/
  --    ON StateProvinces.StateProvinceID = Cities.StateProvinceID
  WHERE    CityName IN (
               'Nashville', /* Nashville serves great chicken */
               'Cleveland', /* Wonder if he meant Ohio?*/
               'Richmond',
               'Atlanta',
               'Milwaukee',
               'Seattle',
               'Orlando' )
  ORDER BY Cities.LatestRecordedPopulation DESC;

Listing 4

Those unruly comments will still make comparing code a bit messy, though, so let’s deal with them next.

Removing Comments

I’m a big fan of adding terse, meaningful comments to code to help other users understand the intent of the code or why certain choices were made. However, when you need to compare two versions of the same code to understand the cause of behavioral differences, then comments — especially verbose comments — are unnecessary clutter.

Usually, you will only remove comments temporarily, but not always. For example, some developers don’t like comments committed to source control, as it makes it harder to spot meaningful changes.

Removing comments is regarded as code refactoring, so it isn’t part of the style options, nor is it one of the formatting actions that we can apply automatically when we run Format SQL. Instead, we simply remove them on an ad-hoc basis using the Actions list. Within SSMS, run Format SQL to apply the Code_Comparison style, and then highlight the code and select the Remove comments action from the Actions list, as seen in Figure 2.

SQL formatting 2

Figure 2

The final code will look as shown in Listing 5.

SELECT
      StateProvinceName AS StateName,
      Cities.CityName,
      SalesTerritory,
      Cities.LatestRecordedPopulation AS LatestRecordedCityPopulation
  FROM
      Application.Cities
      JOIN Application.StateProvinces
          ON StateProvinces.StateProvinceID = Cities.StateProvinceID
  WHERE
      CityName IN (
          'Nashville',
          'Cleveland',
          'Richmond',
          'Atlanta',
          'Milwaukee',
          'Seattle',
          'Orlando' )
  ORDER BY 
        Cities.LatestRecordedPopulation DESC;

Listing 5

When you are finished reading the code, you can revert to the original version by using Ctrl + Z, or Edit\Undo, to reestablish the code comments, and then switching back to the original style and running Format SQL. Alternatively, you can just perform two undos: one for the comments, one for the formatting.

Summary

SQL Prompt’s custom styles make it easier to apply different custom formatting to your code, depending on your current requirement, even if it’s just because you find the agreed, corporate code formatting standard impossible to read.

Even if your primary corporate SQL coding standard cannot be replicated using SQL Prompt, you can still use formatting templates to allow you to view code in an alternate style. When you’re done, you can simply revert to the committee style and run Format SQL.

However, if you want to make changes to the code that falls outside what’s defined within a custom style, for example using actions to remove comments to perform code comparisons, or to add or remove square brackets, then you’ll need to remember to “undo” these changes before, for example, committing the code back into the version control system. In such cases, it’s probably wise to make a copy of the code first, in a new query tab, so you can be sure you always have the original available. SQL Prompt’s Tab History feature (bar none, the one feature of SQL Prompt that I can no longer live without) will restore previous tabs, even if you close them without saving them.

My next article will shift the focus from making code look better to the ways in which we can use SQL Prompt to improve the quality of the code, such as by making sure all column references are qualified to the parent object.

Align DevOps for your applications with DevOps for your SQL Server databases to increase speed of delivery and keep data safe. Discover true Database DevOps, brought to you in partnership with Redgate

Topics:
database ,sql ,sql prompt ,code formatting

Published at DZone with permission of Louis Davidson, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}