How you can improve your SQL with code analysis in SQL Prompt

You could think of SQL Prompt code analysis as a machine-assisted code review. The rules that are used to check your code point out questionable areas that might be missed during regular testing.

Code analysis is a formal automated process of scanning a piece of software code and deducing potential problems, issues and faults that may not be apparent to programmers at first glance.
These could include mistakes that are easy to make for but hard to detect (such as copying and pasting something that is incorrect), the usage of obsolete elements in the code, or bad practices that, while technically correct, don’t produce the best or expected results.

The advantages of using code analysis include:

  • Early detection of bugs and code smells in the development cycle, which means less time and money spent on fixing them.
  • Discovery of problems in related or adjacent areas of code.
  • Detection of anti-patterns and deprecated elements in code.
  • Detection of security or performance issues.
  • Use as a learning tool that shares knowledge across the team. This can be of particular value for more junior members and people new to SQL development.
  • Identification of areas in the code that need to be refactored or simplified.
  • Highlighting of areas of the code that may need more testing or a deeper review.
  • Propagation of best practices and team standards, which ensures codebase consistency.
  • Identification of potential software quality issues before the code moves to production.

Introducing code analysis in SQL Prompt 9

There is a distinct lack of code analysis tools in the SQL development world, despite their wide availability in other programming languages and environments (such as .Net, C/C++ and Java).

In June 2017 we acquired SQL Code Guard, with the aim of adding code analysis functionality to our existing tools. We’ve already added performance rules in SQL Monitor, and now you can get fast and comprehensive code analysis in SQL Prompt as you type.

When you upgrade to SQL Prompt 9 you will start to see the new features in your SSMS or Visual Studio query window as you start typing.

You will get a green wavy line under any parts of your script that have a code issue.

These can be selected by clicking on them, with a quick tip displayed when you hover your cursor over the issue:

SQL Prompt 9 - Code Analysis quick tip

For a more detailed description of the issue, click on the blue lightbulb icon that appears to the left of the line you are on. This brings up the Issue details panel, which gives you more information on what the problem might be and how you can fix it.

The Issue details panel also contains a link to further online documentation, in the form of a library, that provides further reading and learning resources on code issues.

SQL Prompt 9 - Issue Details

If you find this particular rule isn’t helpful or you don’t need it right now you can click on Disable rule to stop it displaying.

This can be done for individual suggestions as they appear, but a quicker way of managing your rules is through the Manage code analysis rules… options that can be found in the SQL Prompt menu.

SQL Prompt 9 - Manage rules

From here you can turn rules on and off using the check boxes alongside them. The settings file for this can also be shared amongst your team, so you can all work with the same rules active, which can help with code consistency.

What’s next for code analysis in SQL Prompt?

We will continue to expand code analysis functionality in SQL Prompt by adding new rules until we have parity with SQL Code Guard.

We are also keen to hear about new rules you’d like added to SQL Prompt code analysis. Head over to UserVoice and add your suggestions for future releases.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly in SQL Server Management Studio and Visual Studio.

Find out more

Share this post.

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

You may also like

  • 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

  • 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

    Consider using [NOT] EXISTS instead of [NOT] IN with a subquery (PE019)

    Phil Factor explains why you should prefer use of EXISTS over IN, when comparing data sets using a subquery. While there is no longer any significant performance advantage, using NOT EXISTS will avoid unexpected results when the subquery’s source data contains NULL values.

  • 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.

  • 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

  • Andrew Chandler-Wilson

    Looking forward to trying out the Code Analysis, but also worried what I might find!