David Atkinson

7 June 2016

Is your SQL code covered?

No, it almost certainly isn’t.

Besides, how would you know either way? Let me explain.

Most modern software development today includes some form of automated testing. While comprehensive unit testing is still sorely lacking in many organizations, it’s often mitigated by other forms of automated testing conducted later on in the development cycle.

Code coverage tools (sometimes known as test coverage tools) measure the extent of untested code in your codebase. You can see how well covered with tests a particular area is and make informed decisions on any additional tests that may need to be written.

What has this got to do with SQL?

Everything, because even though your SQL database contains code objects, such as stored procedures and functions, they’re probably not covered by your existing tests. The vast majority of code coverage tools only examine the application code.

At Redgate, it’s our mission to ensure database developers have access to the same tools and practices applications developers have benefited from for such a long time. So we teamed up with Ed Elliott to build SQLCover, an open source code coverage solution for SQL Server that lets you see exactly how many statements in your database code were executed when you ran your tests(s).

SQLCover has been designed from the outset to integrate seamlessly with Redgate’s Database Lifecycle Management (DLM) toolchain, helping code coverage become an integral part of database continuous integration. It has first class support for tSQLt and, because it’s implemented via PowerShell cmdlets, it has the flexibility to be used with any test automation framework such as NUnit or Selenium.

What level of code coverage should I be aiming for?

The ideal code coverage is 100%, right? After all, who wouldn’t want all their code to be tested?

Like all other testing efforts, it comes down to prioritization and sensible investment of resources. It’s clearly essential to have thorough testing on code that’s run frequently or is critical to the smooth running of your application. But it may be less crucial to cover unaltered code that has been merrily running in production for years without causing any issues. If that code is ever refactored or its behavior modified, that would be a sensible time to consider adding code coverage. But as Martin Fowler’s blog points out, the quality of your tests is more important that the percentage of test coverage you have in place.

Stuart Kent, who works for Microsoft on ways to measure technical debt in Visual Studio, once suggested you shouldn’t strive for perfection when attempting to put code quality measures in place. Instead, you should set the baseline threshold at the current level of ‘debt’, with the short and medium term goal of not getting any worse. Only when the team has enjoyed prolonged and sustainable success with maintaining the status quo should they start thinking about improvement.

Getting up and running with SQLCover

If you’d like to explore generating your own code coverage reports as part of a database CI process, take a look at this step-by-step worked example Using SQLCover with DLM Automation.

You can also read more about SQLCover and code coverage in general at Ed Elliott’s blog.

And to give you some idea of the output you can expect from SQLCover – and what you’re missing without it – here’s a sample: