Tom Crossman and Andrei Rusitoru

8 March 2017

How to customize your formatting styles with SQL Prompt Pro

SQL code formatting is a very popular SQL Prompt feature. For many years, it’s done a great job of transforming illegible SQL code into a layout that’s very readable for most users.

That said, many users have been asking us for greater control over their formatting styles. Take a browse through our UserVoice page, where we collect product feature suggestions, and you’ll see that formatting improvements are some of the most requested ideas for SQL Prompt.

In this short article, we review the main drivers behind the changes we’ve made recently, how we rewrote the formatting engine to support these changes, and then take a look at how the new formatting options work.

The need for custom SQL formatting

We started trying to tackle this problem by surveying SQL Prompt users, to understand how they like their code to be formatted. SQL developers often have a preferred style in which they like to see their SQL code laid out, including preferences for keyword capitalization and line-indenting.

Our survey helped us understand that many developers also have specific preferences for how their CASE statements, WHERE clauses and JOIN conditions are formatted, as well as how particular database objects are formatted, such as functions or CTEs.

We also found that developers might have a style they use while they’re working, but it might be different to their company or team style.

All of this called for a much more flexible and extensible approach to our support for SQL code formatting styles within SQL Prompt. The tool needed to let users customize styling, and then save, reuse and share their resulting ‘custom styles’. It needed to offer the ability to switch easily between styles, but also for teams to impose a uniform style for certain, specific purposes, such as committing code to source control.

Revamping the SQL Prompt formatting engine

When we reviewed the sheer range of different styles our users were requesting, it became clear quite quickly that it would be very difficult to support all of these feature requests by simply extending the existing formatting options. Our formatting system hadn’t been given an overhaul since 2009, which meant it was becoming difficult for us to make changes and add features as quickly as we would have liked.

Faced with such a large number of feature requests, we decided to create a brand new formatting engine and user interface.

We started by building a prototype, followed by a series of usability sessions to get feedback on the changes we’d made. Following some beta releases, and lots more feedback from users, we released SQL Prompt 7.3 in December last year which included the new formatting options as an experimental feature.

Let’s take a first look at how the new formatting options work.

Introducing the new formatting options in SQL Prompt

In the new version, formatting styles are much more customizable. There are now many more options for formatting, including:

• CASE statements
• JOINs
• CTEs
• CREATE TABLE statements
• IN values

… and much more, as Figure 1 demonstrates:

Figure 1

Styles

The new user interface makes it easy to work with formatting styles. You can quickly browse through available styles, create new ones, and choose which one to use as the active style (the style that will be used when you run Format SQL).

You might want to use different styles for different purposes, such as reviewing code versus committing code to source control. SQL Prompt’s new formatting styles let you switch between these styles with ease.

Figure 2

You can also switch styles simply by right-clicking in a query window.

Figure 3

We’re also planning to add a set of default Redgate styles that you can use as a starting point for creating your own style.

Sharing styles with your team

It’s now easier to share styles with your team. Styles are defined as XML files, which you can put in a shared folder or location, so your team can apply the same formatting to their code. To set your style folder to a custom location you can easily change the style folder.

Enabling custom SQL Prompt styles

If you’re using SQL Prompt 7.3 or later, you can enable the new formatting engine right now through the SQL Prompt experimental features options menu.

To start using the new formatting styles:

1. On the SQL Prompt menu, select Options
2. Go to the Labs > Experimental Features page
3. Select the Use new formatting styles checkbox

Figure 4

We can’t stress enough that all of the improvements were almost entirely driven by user feedback, and wouldn’t have been possible without it. Please do continue to let us know what you think, and how we can improve these new formatting options still further.

We’re planning to fully integrate the new formatting styles into SQL Prompt Pro very soon.

SQL Prompt Pro lets you write, format, refactor, and share SQL effortlessly. A part of the SQL Toolbelt, it encourages collaboration and teamwork, while stripping away the repetition of coding.

If you’re not a SQL Prompt Pro user, download a free trial and see how much more productive you can be writing code every day.