Maintaining T-SQL Code Quality Using SQL Code Guard

Sometimes you need some functionality in your database build process that isn't quite available. Visual Studio Database projects don't have great ways of reviewing SQL code for potential errors, but is it possible to adapt a good command-line tool like SQL Code Guard that does the job well? Lukasz shows how to do it by using NuGet packages to integrate such tools with the project and the build process.

What, where, why?

When I joined Objectivity I was assigned to the internal team where, among other tasks, I had to review and refine existing SQL code in one of internal projects to improve its quality. Visual Studio contains a simple SQL syntax validator, but it cannot detect less obvious mistakes (Like deleting all rows without any filtering by WHERE clause, inserting without providing a column list, using TOP(x) without ORDER BY clause, or even forgetting to drop a temporary table), so we had to find, and use, another tool. For other languages, there are great tools such as FxCop, StyleCop, JSHint/JSLint, ReSharper-which are easy to use, and can validate the syntax as well as check the style of coding. Unfortunately, there are no such commonly accepted, or widely used tools for SQL.

At one point I was told to use SQL Code Guard tool (which can be found here: https://www.red-gate.com/products/sql-development/sql-code-guard/). The tool is, and I’ll quote, “a free solution for SQL Server that provides fast and comprehensive static analysis for T-SQL code, shows code complexity and objects dependencies”. Sounds great so far.

The tool contains a command-line application and an assembly with an MSBuild task. They can both process whole directories recursively, while the app can also parse single files. Both methods weren’t quite good enough for us. Most importantly, we didn’t want to force developers to install anything-so installing and integrating SCG with VS was out of the question. The tools seemed useful, but they couldn’t process a list of files, just a single file or a whole directory. Although it generally could be useful to process a directory, we couldn’t find a way to specify the files that we didn’t want it to check, such as the files excluded from the Visual Studio project. So I had to think up another way to use it.

Preferred approach

The idea was to integrate the tool into the build process, so warnings and errors would be shown like any other compilation error, e.g.:

2347-2.png

Moreover, errors should appear in a format that Visual Studio would recognize and could additionally show in the standard Error List window, as you can see below (Actually, the first shown error is from built-in VS validator. The next three warnings are from SQL Code Guard):

2347-test.png

Integrating into the build process is not easy because it requires manual changes in the project file (such as using additional MSBuild tasks or importing extra target files). I knew that StyleCop and FxCop do that, so I decided to look into what exactly they do in more detail. It turned out that they-like many other tools-use NuGet packages to integrate with the project and the build process. Moreover, NuGet packages are widely used, so this approach seemed the most hopeful one to help us to build our tool. There is no need to install additional software, maintain it, or check on various websites for updates. NuGet does it all for you.

The challenge

To accomplish the goal I had to learn how to build the NuGet package. I’ve never done it before, but it didn’t look to be particularly difficult. I looked at the BuildTools.NET (Available at https://github.com/jonwagner/BuildTools.NET) – which is used by BuildTools.FxCop and BuildTools.StyleCop NuGet packages-and I used it as a base to create a BuildTools.SqlCodeGuard NuGet package. This step required from me to learn basics of PowerShell as well, because the BuildTools uses PowerShell scripts to modify MsBuild files during NuGet package installation and uninstall.

Having a NuGet package that does nothing is not good, so the package obviously had to contain the actual tool. To use SQL Code Guard easily from the project, I had to prepare an MSBuild target file which would invoke the tool for each piece of SQL code (that’s right, not for each SQL file). I’ve learned the basics of MSBuild and PowerShell and prepared suitable MSBuild target file I could embed in the package.

The current target file invokes the command-line tool for each SQL file, and for each SQL script embedded in RESX files (the database test project stores SQL code there)-each code fragment is extracted from the RESX file by a PowerShell script. Because I manually invoke the tool, I can also parse its output and rewrite it in a way that Visual Studio understands-we want the VS to show warnings and errors, if there are any: and unfortunately usually there are some.

Known issues

Yes, all tools have their known issues, even this one. Nothing is perfect.

The biggest issue is that NuGet packages are not officially supported by database projects (*.sqlproj). To use this tool with that kind of a project one must manually edit the project file to add necessary code (The best way to do it is to create two empty C# projects, install a NuGet package into one of them, then compare them, and apply found differences to the database project). Each NuGet package upgrade also requires re-edits and restart of VS. This is easier if the solution also contains other type of project (NuGet supported) from which one can copy necessary code to the database project. Otherwise, it can be tedious.

The other minor issues are:

  • The tool requires at least .NET Framework 4 installed.
  • The location of errors/warnings is reported better if the code is indented with spaces, not tabs.
  • Sometimes there are false errors reported, e.g. when SQL Code Guard doesn’t understand the syntax well (mostly from the newest version of SQL).
  • If the checking for semicolons after each statement is enabled, the tool requires two of them after the WHILE statement.
  • There’s also one heisenbug, for sometimes warnings/errors disappear from Error List window… so one should look for them in the build output window.

Summary

The built NuGet package was so successful that it’s already been used in our four projects. This has helped with finding its weaknesses, and those areas which can be improved in the future. One of these weaknesses is its performance. The initial analysis can take more than ten minutes to complete if the project contains hundreds of SQL files. That’s why I’ve implemented caching of results. Each next build will trigger the validation of only the changed files. Future improvement in this area might include parallel execution which should speed-up first analysis on multi-core devices.

The other improvement is to integrate with our PSCI library (PowerShell Continuous Integration and Deployment Automation library, https://github.com/ObjectivityBSS/PSCI), which will enable code analysis outside Visual Studio or MSBuild-based projects. I hope I will handle that in the near future, time permitting.

If you’d like to try to use our NuGet package, you can find it at https://www.nuget.org/packages/BuildTools.SqlCodeGuard/. It’s not perfect, but any comment and suggestions are more than welcome (you can contact us using Contact Owners link available on the page). The source code is available at https://github.com/ObjectivityBSS/BuildTools.SqlCodeGuard.