Exploring the Caves of Code Analysis in #SQLPrompt

Steve Jones, 2018-04-25 (first published: 2018-04-16)

I enjoy themes, and when I ran across the SQL Prompt Treasure Island, I had to take a few minutes and go through it. I wrote about a few of the items, and this post continues on with a feature that was added last year to SQL Prompt, Code Analysis.

Code Analysis

One of the big leaps forward for computer science, in my opinion, was the development of various static code analysis (SCA) tools. These are automated programs that examine the structure of source code and look for potential issues with the way the algorithms are implemented. This was originally the job of a fellow programmer, and still is in many cases, but humans make mistakes, and reviewing someone else’s code is a tedious, somewhat boring task. Over time, many humans become worse at it as we look for certain issues, but may ignore others.

Over time, SCA tools have included scanning for potential security issues, such as buffer overruns, which can easily permeate many systems if the developers do not follow coding practices designed to avoid issues. I wish we had such advances in SQL tools, but they’re not here yet.

In SQL Prompt v9, Redgate added some SCA features. These were a set of rules that are used to scan your T-SQL code for potential issues. such as casting data types without specifying a length, or as the Caves of Code Analysis post shows, forgetting to qualify an object. In my example below, the green squiggly line below the code represents an SCA finding, and as I’ve hovered over the line, I see the warning about an old style join.

2018-04-06 08_39_06-SQLQuery1.sql - DKRSPECTRE_SQL2014.SimpleTalk_1_Dev (DKRSPECTRE_way0u (55))_ - M

Each of the rules is designed to highlight some issue that is known to be a potential problem. For the most part, these are useful tools and you should use these to examine your code. Some, however, aren’t useful and can be annoying.

There is a dialog that allows you to enable or disable any of the rules, which are divided into different types. I often disable ST002, which deals with aliases. I prefer the old style equal sign as opposed to the AS syntax. This isn’t a code issue, so I don’t need the warning.

2018-04-06 08_45_50-Sql Prompt - Code analysis rules

This is a basic set of SCA tooling for T-SQL code, but it does serve to educate newer developers about the dangers of using certain patterns in their code. It also reminds experienced people if they’ve done something like used COUNT() in a test instead of EXISTS(). Those types of changes can often improve the overall quality of your code.

Give SQL Prompt a try today and I’m sure you’ll be pleased with just how quicker you can write code and learn about the potential issues you’ve been including in your code.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis


1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren


1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren


360 reads