Alessandro Alpi

9 March 2018

• SQL Prompt
• SQL Code Analysis
• SQL Formatting and Styles

Driving up database coding standards using SQL Prompt

Most of us in the data management industry will have learned to adapt, in recent years, to ‘agile’ development and deployment practices. Many organizations have invested heavily in the tools and processes they hope will allow them to deliver new functionality to users more frequently and reliably, while also maintaining quality standards. To achieve this, we need simple tools that both allow us to work quickly and effectively, with databases, and that encourage consistently-high standards of database code.

I call this writing code from a database-first perspective and, in this article, I will demonstrate how a team can use SQL Prompt to establish and share coding standards, through code analysis rules, formatting styles and code snippets. In my experience, this will, offer many benefits, including:

• More effective team working, and a simpler induction process for new team members.
• Smoother code integration, more reliable deployments
• More predictable performance in production
• Fewer maintenance headaches, less unplanned work

Why a team needs database coding standards

Database DevOps techniques, implemented correctly, allows a transfer of effective practices and methods between operations and delivery teams. DBAs, like me, improve our understanding of version control, change sets, packages, unit tests, and so on…and we’re getting there, finally! Likewise, developers get better insight into the database code and architecture standards that will ensure a company’s databases are available and secure, and with minimized risk of performance and maintainability issues.

It’s a process of gradual improvements, and to succeed we need a simple, shared toolset that will establish common coding techniques, standards and practices, across the teams, and makes it easy for everyone on the team to implement them consistently.

Defining a single set of team ‘rules’, or standards, for various aspects of code design and code formatting, will give you many advantages. For example:

• Code formatting standards – ensure readable, consistently-formatted code that is easier to share and review among the team, and easier to refactor without introducing inconsistencies and errors.
• Standardized structures for code modules – make it much easier to apply consistent approaches to the way the team build and test database code, such as a standard approach to error handling.
• Code quality rules – a shared set of code analysis rules that you run automatically, during development and testing work, will help minimize technical debt, and drive down maintenance costs.

Once the team agree on the need for consistency, the harder part is agreeing on the details of the standards and rules. Having got this far, you still need to ensure that every team member applies the standards, every time they commit code to a shared repository. With a team of ten developers all working on different branches or features of the same shared code base, this is not as easy as it sounds. However, without this final step, inconsistences and mistakes can and will sneak into the code base.

This is where SQL Prompt really helps.

Implement coding standards with SQL Prompt

Developers tend to regard the DBA’s tool of choice, SQL Server Management Studio (SSMS), as lacking in versatility, compared to their application development IDEs, like Visual Studio. Plug in SQL Prompt, and the situation improves dramatically. As well as the SQL IntelliSense and code completion that help write accurate code, faster, it provides many features that will help the team improve productivity, drive up code standards

Over the following sections, I’ll review briefly how SQL Prompt allows a team to implement standards for code quality, code formatting styles, and for code templates and snippets, and the benefits these bring.

In each case, you’ll see that, having defined and configured each ‘standard’, SQL Prompt makes it an almost effortless task to share it among the team, and have each team member apply it. You just save the associated file in a location to which the whole team has access, ideally in the version control system. Each developer ‘points’ SQL Prompt at the shared file, and then every time a team member opens SQL Server Management Studio, he or she automatically has access to the latest version.

That’s all there is to it; from that point each developer can apply the standard with just a few keyboard clicks. The settings become completely shared and standardized between team members.

By following such a sharing practice, any new members of your team can join and be trusted to change, add and write code easily and to the same standards as your most experienced colleagues. The better your team are aligned, the better and cleaner will be your code, meaning you can apply changes with more confidence, and deploy quickly and safely.

Code quality standards using code analysis rules

SQL Prompt now comes with a list of code analysis rules you can run on your queries, as you review them, or as you type them. SQL Prompt will run the code analysis in the background, parsing the code and checking the syntax for compliance with a built-in set of rules.

The rules encourage good coding practices, and to alert the team to code that, for example, flouts code style rules, uses deprecated syntax, or could at some point cause performance problems, unpredictable behavior, or even security vulnerabilities. If code flouts a rule, you’ll see it denoted with a ‘wavy underline’, in SSMS, and you can click through for a description of the rule, and potential problems causes by code that doesn’t adhere to it.

Figure 1

Of course, not all rules will apply to all code bases, and when dealing with legacy code there may be some rule you’d prefer to disable, simply because you can’t fix them at that time.

To configure which rules you wish to apply, simply choose Manage Code Analysis Rules… from the SQL Prompt menu. Select and deselect the required rules, and then hit Save. This will save the local user’s settings file in the location shown at the top of the screen.

Figure 2

Having defined as a team the rules you wish to be enforced, simply click Save As, and save it to a location to which the team has access, such as a folder in the shared source control repo, or just a development shared drive. Then, each team member simply uses the ellipsis button in Figure 2 to locate the team settings file, and hit Save.

Code formatting standards using SQL Prompt Styles

Without standardized formatting our code can become much more prone to human errors and, in the end, reduce the quality of the product or service we’re delivering in production. Minimizing errors dramatically improves the quality of our work and our product.

SQL Prompt allows us to define styles, where each style describes exactly how we wish to lay out our SQL code. We use the Edit Formatting Styles menu item to create new custom styles and edit existing ones.

Figure 3

When you click in to edit an existing custom style, you’ll be able to set a range of styling options that control keyword casing, and how the various clauses, conditions, list items, and so on, are aligned. All these options affect only white-space, such as carriage return/linefeeds, tabs and spaces.

Having configured your ‘team style’ you can share the settings file just as easily as for the code analysis settings, just by storing the file in a style folder, in shared location, and having each developer access the file, from the local client. Having done this each team member simply sets the Team Style as the Active Style in SQL Prompt, and then applies it with a few keyboard clicks (Ctrl K, Y). Of course, each team member can also use their own custom styles locally, if they always switch to the team style and apply it, before sharing code with others.

Standardized code modules with Prompt code snippets

SQL Prompt code snippets define the basic structure for a block of code, or of a T-SQL statement that creates the common types of database objects. You can invoke a snippet from any point in a query pane, to inject the snippet into your code.

Not only are they a great way of saving and reusing useful code, will also help drive up code quality. More experienced team members can share snippets that help introduce standards and consistency to the way the team build modules, for example in terms of structure of any headers, inclusion of guard clauses, and so on. This will make code safer to execute, easier to understand and faster to test.

Standardized snippets for testing code, or investigating slow-running code, will mean the results will always be provided in a standard format, which makes for much easier team collaboration during troubleshooting.

Again, sharing useful snippets with the team is easy. Snippets are stored as XML files with the .sqlpromptsnippet file extension, so just create a Prompt Snippets folder in a location to which everyone on the team has access, and place in it any custom snippet files you want to share, as well as all the built-in snippets that the team use. Each team member adjusts their Snippet folder location to point access the folder, and the snippets.

Figure 4

Conclusions

SQL Prompt is one of the most advanced tools available for helping us write SQL. It helps us get a new perspective on coding standards for our teams, and it can improve our day job in many ways.

Developers who have worked on a code base for a long time may find themselves falling into bad habits. So, by proactively alerting them to code issues, and giving them ways to check and auto-format their code, you can help them to follow the same standards as the other team members without impacting on their productivity.

Likewise, new developers will be more productive from very early in a project, writing database code that adheres to good quality standards.