Cathrine Wilhelmsen

2 February 2018

• SQL Prompt
• SQL Code Analysis

Avoid T-SQL Technical Debt using SQL Prompt Code Analysis

SQL Prompt’s Code Analysis feature helps you discover code issues and hidden pitfalls during code development, as you type. It also provides tips for improving your code, and includes links to documentation that offers advice on the about each of the supported rules, so you can decide on the best course of action. After you have installed or upgraded to SQL Prompt 9, you will see two new menu items: Enable Code Analysis, and Manage Code Analysis Rules. Make sure that Enable Code Analysis is checked.

When you open an existing query, or begin typing in a new window, the Code Analysis feature will start working its magic. It will parse your code in the background and check the SQL syntax against a built-in set of rules. These rules are designed to catch immediate problems which may cause code to fail or execute incorrectly, root out potential performance problems, highlight deprecated or old-style syntax, and encourage best coding practices. Code issues and suggestions for improvements are displayed in the query editor as green squiggly lines under the offending code.

If you click on Manage Code Analysis Rules, you will see the full range of different types of rules that SQL Prompt currently supports. It is also where you can control which of the rules you wish to enable or disable.

Currently, all rules are global, but I expect subsequent releases to allow finer-grained control. For example, developers may want to configure rules per server or database, just as you can do for other Prompt features like Tab Colors. They may also want to be able to establish, and switch between, multiple sets of rules, depending on the project they are currently working on, in a similar fashion to using and swapping between multiple code formatting styles.

Breaking the Rules…

Let’s see SQL Prompt’s Code Analysis feature in action, by breaking a few rules. This is our original query:

When we open the script in SSSM, we can see that SQL Prompt has highlighted a few issues! Oh boy, look at all those green, squiggly lines. Don’t try this query at home!

Let’s see how we can use Code Analysis to improve our code. Hover over TOP 10, and we see that this code uses an old-style TOP clause:

When we click on TOP 10, the code is highlighted green and a blue icon with a lightbulb appears. While the code is highlighted green, we can either click on the lightbulb icon or press Ctrl to see details.

The Issue details window pops up and tells us more about the issue, and we can go to the online documentation for more information, or we can choose to disable the rule.

If we choose to disable the rule from the Issue details window, a red disabled icon appears and the green squiggly line disappears.

Next, Code Analysis tells us that we have unqualified column names:

The Issue details offer advice: “A column name should be qualified with its table name if it is part of a join or is in a subquery. Even if it is currently unambiguous, at some point a change could be made that will cause an error.”

To solve this, we first give our tables aliases. Then we save ourselves some time and use SQL Prompt’s built-in Qualify Object Names (Ctrl+B, Ctrl+Q) feature instead of qualifying the object names manually.

We are making progress! There are only two issues left in our query. The first issue is that we are using an old-style join syntax.

Style rules, more than any other category, might sound more like personal preferences than strict rules. However, breaking good style practices can cause more trouble than you might think, as it often makes your code harder for others to read and understand, and sometimes makes it easier to introduce errors.

While the old-style inner joins are still supported, they offer no advantages over ANSI standard JOIN syntax. Also, what if you accidentally remove or comment out the WHERE clause while changing other clauses? The query will still happily execute, but you now have a cross join instead of the intended inner join, which may lead to incorrect results.

To solve this, we rewrite our query to use an ANSI-standard JOIN syntax. A helpful side effect of using explicit joins instead of old-style joins is that SQL Prompt can suggest and insert the ON clause for us.

Finally, Code Analysis tells us that the != operator should be replaced with the <> operator.

We have now chosen to disable certain rules and change our query to follow the other rules and best style practices. The final step could be to use SQL Prompt’s Format SQL (Ctrl+K, Ctrl+Y) feature to adhere to formatting styles. The final query looks like this:

By working through the Code Analysis suggestions, we have now learned – or been reminded – that we should use the recommended parenthesis in TOP clauses, qualified object names, explicit joins, and ANSI-style operators.

My code flouts some rules, what do I do?

The first place to start is the Code Analysis documentation that you will see in the Issue details window. For some rules, the documented description of the rule should be enough for you to understand how to fix the code.

However, for other rules, you may need more guidance, and to do a bit of research, before deciding on the correct action. Redgate is in the process of building out the information supplied for each rule, and you can expect to see a library of articles developed that offer more practical advice on how to deal with these issues. If you are unsure about anything, follow Microsoft’s advice in the Transact-SQL Reference.

Summary

SQL Prompt 9 was launched with over 60 code analysis rules, which check your code for best practices, deprecated features, impact on performance, old styles and more. This is just the first version of the feature. As it evolves, I personally hope to be able to enable/disable rules per server and database, switch between sets of rules depending on the project I’m working on, and eventually automatically fix issues in my code based on suggestions.

Nevertheless, SQL Prompt’s Code Analysis feature has already helped me identify several issues and potential improvements in my code, and reminded me to also change the code in some of my SQL Prompt Snippets. It is a great feature that I know will help teams prevent the accumulation of technical debt in their code base.

Of course, never rely on a tool alone. Tools can help you, but always make sure that you understand why a tool is making certain suggestions. Use the tool as a reminder, then become a better SQL developer by learning from it 🙂

If you have any other ideas or suggestions for the Code Analysis feature in SQL Prompt, let Redgate know by posting or voting on UserVoice or the SQL Prompt forums!

SQL Prompt 9.2, and later, extend the code analysis feature to include a list of code analysis issues, designed to make it much easier to do code reviews and to analyze and improve legacy code. It allows users to analyze an entire script, no matter how large, and see a list of all the issues contained within it. Tom Walsh explores this use of Prompt’s code analysis in his article, Improving legacy code using SQL Prompt code analysis