7 June 2016
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:

sample SQL Cover code coverage coverage output

Tools in this post

DLM Automation

Continuous integration and automated deployments for your SQL Server database.

Find out more

Share this post.

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

Related posts

Also in Blog

The future of database DevOps

Foundry is Redgate’s R&D division. Part of our remit is to look further into the future. We do that by keeping an eye on things that might indicate a change in what you need from our products (e...

Also in DevOps

5 challenges to scaling DevOps at enterprise level

Deciding that your organization needs to kick-start its future growth plans with a digital transformation initiative is as exciting as it is daunting. No matter what your industry – financial servic...

Also in Software development

Would you like to see Redgate tools inside SQL Operations Studio?

You may already have heard about Microsoft’s new DevOps platform for database development, SQL Operations Studio, or “SQL Ops Studio”. The preview version was announced at PASS Summit on 1 Novem...

Also about continuous integration

Database CI with Redgate DLM Automation, PowerShell and Jenkins

While the practice of Continuous Integration (CI) started with application code, you can apply the same principles to databases. Database CI is the process by which we build, test and deploy the datab...

Also about DLM Automation

Database automation made easier

Last week saw the release of DLM Automation - a new improved version of our database automation offering. So what’s changed?

DLM Automation combines all the functionality of its predecessors, SQL C...