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

How to Implement a T-SQL Code Formatting Standard Using SQL Prompt

DZone's Guide to

How to Implement a T-SQL Code Formatting Standard Using SQL Prompt

Determining the team formatting standard is a long process. But once it's done, applying it consistently can be straightforward using a tool like SQL Prompt.

· Database Zone
Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

To what extent should a team attempt to impose a uniform SQL coding format? This 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 10 programmers, though, and you will likely discover at least 15 different formats since few developers are particularly consistent in applying their preferred style.

If those 10 programmers are co-workers on the same project, how do you agree on a standard style and then implement it consistently? One way is through Draconian rules, meeting after meeting, and the occasional sacrifice of a programmer to put the fear in the rest.

If that doesn’t sound like fun, here’s my suggested alternative. First, agree on the details of how exactly code should be formatted. If you fail to reach a consensus, then you use the Louis Davidson style, the basics of which I demonstrate in this article, as a starting point. Or, maybe just print it out and then beat people around the head with it until they submit.

Second, use a standard code formatting tool (in this case, SQL Prompt) to save a defined style as a shareable template so that every programmer can apply it to their SQL code with a few clicks.

Why Impose a SQL Formatting Standard?

Every programmer likes to read code that’s set out in a certain way. A programmer’s individual style is influenced by the formatting preferences of the first language they learned. A Java programmer will tend to format SQL as if it were Java, for example, and use a rather different style than a native Perl programmer. Other factors have an influence too, such as the size of the device on which the programmer generally views the code.

So, why even attempt to impose a single SQL formatting standard? Here are two good reasons:

  1. Ease of reading. If everyone’s code is formatted the same way, it will be much easier for any team member to review another’s code with minimal confusion. Complex Boolean expressions, particularly in WHERE and JOIN conditions, can be hard enough to fathom without the added complication of inscrutable formatting.
  2. Ease of code comparisons. Often, you want to compare different two versions of the same block of code and work out what changed. That’s a much harder task if the person who revised the code used an entirely different formatting style from the original author.

Having established the need for some consistency, the hard part is deciding exactly what your team’s standard will be. It’s worth enduring the effort and the occasional tantrum required to reach an agreement. As Oscar Wilde may have said, had he been a programmer, the only thing worse than a code formatting standard is no code formatting standard.

Choosing a SQL Formatting Standard

Achieving any sort of standardized formatting while the team is writing code is a tremendous pain, largely because SQL can be formatted and still be readable, using countless different formatting options and styles.

SQL was always intended to be close to a real declarative human sentence, and while many believe it ought to be formatted as such, one can take this too far, making the code look like the writing of an author shunning all proper punctuation and capitalization.

Listing 1 shows a simple SELECT statement using the WideWorldImporters sample database. The code will execute and return the twelve states in the U.S. that have a city named Nashville, but I think most would agree that the formatting is atrocious, based simply on the idea of fitting as much code on a line as possible and letting things wrap:

SELECT StateProvinceName StateName,Cities.LatestRecordedPopulation , SalesTerritory   FROM   Application.Cities JOIN Application.StateProvinces ON StateProvinces.StateProvinceID = Cities.StateProvinceID WHERE CityName = 'Nashville' ORDER BY LatestRecordedPopulation desc;

Listing 1.

If we can agree that there are very few (employed) programmers that would adopt this zero-formatting approach to code, I bet we can’t agree on how exactly it should be formatted. There are a lot of choices to consider, such as:

  • How to format and align the main clauses and sub-clauses. Personally, I like primary clauses aligned on the left, and everything else indented to line up on the clause.
  • System function/keyword capitalization. Having them capitalized makes it clearer where the built-in elements are located, even if it’s a pain to type manually.
  • Number of columns per line in the SELECT clause. Either one column per line or as many as will fit on a line. I generally like the code to be as compact as possible without wrapping on a standard programmer’s monitor.
  • Alias style. I typically prefer using expression AS Alias, but I have acquaintances that have argued strongly for Alias = expression.
  • Leading or trailing commas. Commas, much like in writing, are another place where strong opinions are exposed. I will use trailing commas, as this is usually the desired approach.

I could go on further, but this should be enough to establish that there are a ton of choices one could make when formatting code. Expand the question to all DML and DDL including tables, procedures, functions, and so on, and your team members could find a million formatting variations for each piece of code.

For what it’s worth, Listing 2 offers this writer’s opinion on how the query should be formatted. At this stage, apart from adding an AS for the alias and a semi-colon terminator, I’m restricting myself to making the code more readable only, without substantially changing the text. At this stage, I’m avoiding the urge to make this better code (rather than just better-looking code), such as by using proper object qualification and aliasing on all column names:

SELECT StateProvinceName AS StateName, Cities.LatestRecordedPopulation,         
       SalesTerritory
FROM   Application.Cities 
         JOIN Application.StateProvinces 
            ON StateProvinces.StateProvinceID = Cities.StateProvinceID 
WHERE  CityName = 'Nashville' 
ORDER BY LatestRecordedPopulation DESC;

Listing 2.

Having agreed on how the SQL code should be formatted, the next job is to ensure that any code made available for consumption by the team has the formatting standard applied. This is where a tool such as SQL Prompt can help. In the coming sections, I’ll demonstrate how to use it to implement a basic, minimal coding standard for sharing with the team.

Of course, you still need every team member to use the tool and apply the standard in a consistent manner. Enforcement will mostly be achieved with code reviews and teamwork.

SQL Prompt Formatting Options

After installing SQL Prompt, it’s available directly from the SSMS menu. I’m using the latest version, which at the time of writing was v7.5.0. If you want to see the formatting it applies, right out-of-the-box, simply paste into SSMS the poorly formatted code in Listing 3:

set statistics io on
EXEC website.ActivateWebsiteLogon @PersonID = 1, @LogonName = N'Bob',  @InitialPassword = N'secret!'
SELECT StateProvinceName StateName,Cities.LatestRecordedPopulation,SalesTerritory   FROM Application.Cities JOIN Application.StateProvinces ON StateProvinces.StateProvinceID=Cities.StateProvinceID WHERE CityName='Nashville' ORDER BY LatestRecordedPopulation desc

Listing 3.

Highlight the code and choose Options in the tool menu to see what formatting options the tool offers. Figure 1 shows the overview Styles screen.

SQL code formatting 1

Figure 1.

Down the left-hand side, you’ll see many options to control how SQL Prompt makes suggestions, formats code as you type it in, applies formatting to existing code, and helps to manage your various tabs in SSMS.

NoteL In this article, we’ll focus only on the Format section, but note that within the Labs | Experimental Features section of Figure 1, you’ll find the option to enable some new formatting styles that allow more granular control over formatting. I don’t have these options enabled for this article, as I wanted to offer recommendations for a basic, stable formatting template using only the current options.

At the top of the main screen in Figure 1, you’ll see that you can save different named styles in addition to the default style. This is a very useful feature, as it means you can save different styles for different uses. For example, you can define your personal formatting style for when you’re developing code, the team's formatting style to apply when it’s time to commit the code to version control or when you need to ask another team member to review it, a blog style when you want to share your code with others, and so on. There’s also an option to Export a saved style for sharing with team members (we’ll come back to this shortly).

The Sample SQL tab demonstrates how the selected named style (in this case, default) formats various statements, clauses, and conditions. On the Current Query tab, you can see a preview of the code from Listing 3, as formatted in the default style:

SQL code formatting 2

Figure 2.

Defining the Basic Louis Davidson Custom Style

In this section, I’ll attempt to define my base formatting style template. If I want to create another template for a very specific purpose, such as comparing different versions of the same block of code, I’ll create a new custom style from this base style.

With the Default style selected, click Save… and enter a new style name, in my case Louis_BaseStyle.

SQL code formatting 3

Figure 3.

This creates a new custom style, but one that is currently identical to the Default style, so it’s time to make some customizations to get the code looking exactly as I prefer.

Some changes, such as the ones we will make in the Actions tab, may not show up in the preview, so you need to exit and format the code to be sure that the formatting is exactly what you want. As an example, Insert semicolons is set by default but does not show up in the preview.

I’ll walk through each set of options within the Format section where I’ve chosen to adapt the default settings.

Actions

Check the following boxes:

  • Add/remove square brackets.
    • Remove unnecessary brackets. Our sample code doesn’t have any but trust me, unnecessary square brackets are heinous!
  • Add/remove AS keyword on alias definition for tables and views.
    • Add AS Keyword.
  • Apply column alias style.
    • Using the Alias style, column AS alias.

Data Statements

Place the following on a new line:

  • Check Join condition; don’t select Align join condition with JOIN condition.
  • Uncheck Each subsequent column.

Indents:

  • Align SELECT with INSERT. This is another construct we have not used, but about which I feel strongly!

Commas and Parenthesis

Uncheck the Add space before comma box within the Place commas at end option.

Applying the Custom Style

You can see a preview of the custom style by navigating back to Styles within the Options screen. Let’s now apply the style to our code in SSMS. Close the Options dialog (there is no need to resave your work, assuming you saved the custom style at the start with a new name).

In SSMS, use Cntrl + K, Cntrl + Y, or select Format SQL from the SQL Prompt menu. Now, the code looks as shown in Listing 4:

SET STATISTICS IO ON;
EXEC Website.ActivateWebsiteLogon @PersonID = 1, @LogonName = N'Bob', 
    @InitialPassword = N'secret!';
SELECT  StateProvinceName AS StateName, Cities.LatestRecordedPopulation, 
        SalesTerritory
FROM    Application.Cities
        JOIN Application.StateProvinces
            ON StateProvinces.StateProvinceID = Cities.StateProvinceID
WHERE   CityName = 'Nashville'
ORDER BY LatestRecordedPopulation DESC;

Listing 4.

After you apply the formatting, changes are highlighted in the editor to show you that semi-colons are added, the AS was added to the StateName alias, and DESC was capitalized.

This code looks much better than it previously did, but there are a few things that you may want to do manually, such as adding a CarriageReturn-LineFeed (CRLF) between statements. For example, you probably would want to change:

SET STATISTICS IO ON;
EXEC Website.ActivateWebsiteLogon @PersonID = 1, @LogonName = N'Bob', 
    @InitialPassword = N'secret!';

To:

SET STATISTICS IO ON;

EXEC Website.ActivateWebsiteLogon @PersonID = 1, @LogonName = N'Bob', 
    @InitialPassword = N'secret!';

By the same token, if you had ten CRLFs between the statements, SQL Prompt would not remove any of them.

Sharing Custom Styles

Once you have your style configured, you can export it and distribute to the team. The Export option next to the Style drop-down box exports just the custom style, which is what we want to use here. You can save the file anywhere — perhaps to a shared file location to which your team has access.

SQL code formatting 4

Figure 4.

The Import… and Export… options at the bottom of the Options dialog will import and export all Prompt settings, including Suggestions, Inserted Code, Tabs, etc., along with the Format settings that are in use. However, where a coding formatting standard will be unique for the team, some of the other settings may be a more personal choice.

Any team member who has SQL Prompt installed (like, all of them!) can go into the options and import it, from the Format | Styles section of the Options dialog, as shown in Figure 5.

SQL code formatting 5

Figure 5.

They can apply a custom style to all the code they are working on without losing any of their existing formatting styles (unless they have a custom style with the same name as the one being imported, in which case they will deserve to lose their style for ignoring the warning messages).

Note that to implement your complete strategy using SQL Prompt to shape your code, you will likely want to sync up some of the other options of the tool with the team. For example, the Inserted Code section will control how object names are qualified, how table aliases are generated, and so on.

Summary

Many teams avoid the topic of a standard code formatting style as it is too much work to get everyone using the same format. Determining what becomes the team formatting standard is almost certainly going to be a long, possibly contentious process, but once it is done, applying the common formatting style, consistently, will be very straightforward using a tool such as SQL Prompt. Hopefully, the basic Louis Davidson style I’ve suggested in his article will form a good starting point.

My next article will show how to use custom styles to support not just one standard format, but multiple ones, for diverse purposes. For example, you might want a formatting style to make comparing changes to complex code easier. Alternatively, if you hate the corporate standard, you can apply your own formatting when making changes, and change it back before checking the code into source control.

It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

Topics:
sql ,database ,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 }}