2 February 2018
2 February 2018

Avoid T-SQL Technical Debt using SQL Prompt Code Analysis

SQL Prompt can help prevent the build-up of technical debt in your database code. It alerts the team immediately to issues that might not prevent your code from working as expected, but could eventually cause problems with performance, or maintenance, or even security.

Guest post

This is a guest post from Cathrine Wilhelmsen. Cathrine is a Friend of Redgate and Microsoft Data Platform MVP, working as a Data Warehouse and Business Intelligence consultant.

She loves teaching and sharing knowledge, and is active in the SQL Server and PASS communities as an author, speaker, blogger, organizer and chronic volunteer.

She can be found on Twitter and she regularly blogs about technology issues.

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

Guest post

This is a guest post from Cathrine Wilhelmsen. Cathrine is a Friend of Redgate and Microsoft Data Platform MVP, working as a Data Warehouse and Business Intelligence consultant.

She loves teaching and sharing knowledge, and is active in the SQL Server and PASS communities as an author, speaker, blogger, organizer and chronic volunteer.

She can be found on Twitter and she regularly blogs about technology issues.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    Beyond Formatting: Improving SQL Code using SQL Prompt Actions

    Louis Davidson explains how he uses the SQL Prompt refactoring 'Actions' to qualify object names, standardize the use of aliases, and make other instant changes that improve the overall quality of his SQL code.

  • Article

    Using a Variable-length Datatype Without Explicit Length: The Whys and Wherefores (BP007/8)

    If you declare a variable-length string , or coerce a string, without specifying its length, you can fall foul of ‘silent’ string truncation. Some developers resort to using the (MAX) specification, which is a mistake too. Phil Factor explains the dangers and then offers a workaround for the problem, when you're importing text and simply don't know the correct length of each string.

  • Article

    Finding code smells using SQL Prompt: procedures that lack schema-qualification (PE001)

    SQL Prompt will warn you if it spots use of EXECUTE, without specifying the schema in which the stored procedure resides, because it can cause slower execution times, or even result in running of the wrong procedure.

  • Article

    3 Results Grid features SQL Prompt brings to SSMS

    You may not have heard about it yet, but three helpful features were recently added to SQL Prompt that are activated when you right-click on the Results Grid in SQL Server Management Studio (SSMS). 1. Copy as IN clause This feature is available when you have values from a single column selected. You can select

  • University

    Take the SQL Prompt course

    This Redgate University course takes you from installation all the way up to getting the most out of the advanced operations in SQL Prompt. As well as autocompleting your code, you’ll learn how SQL Prompt can help you with code formatting, object renaming, code analysis, and other useful tip and tricks.

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly