6 April 2018
6 April 2018

Effective Database Testing with SQL Test and SQL Cover

A well-established technique for improving application code quality, during software development, is to run unit tests, in conjunction with a code coverage tool. The aim is not only to test that your software components behave as you would expect, but also that your suite of tests gives your code a thorough workout.

Errors encountered within the most common routes through your logic will usually reveal themselves during the development process, long before they ever reach deployment. It’s in the darker corners that bugs are more likely to live and thrive; within unusual code paths that triggered by specific inputs that the code can’t handle, but which should, in theory at least, never arise in everyday use. Code coverage gives the developers a measure of how effectively they’re delving into these areas.

SQL Test is an add-in for SQL Server Management Studio that you can use to create and run tSQLt unit tests against your database code. The latest release (SQL Test 3) incorporates SQL Cover, an open source SQL code coverage library, written by Ed Elliott with support from Redgate. This article describes the basics of how SQL Test’s code coverage can help uncover the darker, untested paths in your database code.

Getting Started

SQL Test is part of SQL Toolbelt Essentials download, so you can just choose which tools to install as part of the install.

Once installed, you’ll have the option of clicking the create sample database link to install a tSQLt_Example database, consisting of two tables, and a very simple set of tSQLt tests, which can be used as a basic starting point.

However, to demonstrate SQL Test in action, we’re going to look at some tests for the AdventureWorks database. To get started, open SSMS, click on the SQL Test icon, then Add Database, and finally select the database. SQL Test will need to alter the database, enabling SQL CLR, and setting TRUSTWORTHY ON, if appropriate, and installing the tSQLt framework objects into the database. This is a testing tool only; don’t do this in a production environment!

In this example, we’ll also install the SQLCop static analysis tests. These will check properties of the database and its objects for consistency and correctness, but don’t execute any user code, and so will not have any effect on our code coverage.

Enabling code coverage on a suite of tests is simple; just turn it on, as shown in Figure 1.

Figure 1

Then just click Run Tests. Rather predictably, as our SQLCop tests aren’t executing code, our coverage starts off at 0%.

Figure 2

Creating the tSQLt code coverage tests

Now, let’s add some tests that do execute code. As a first example, we’ll pick the database function dbo.ufnLeadingZeros and write some tests to prove its correctness. This function pads an integer value out to an eight-character string with leading zeroes.

We’ll add a straightforward test to execute a successful case. You can just execute the code as shown in Figure 3, or you can click Add Test in SQL Test, and give the test a name, and a class of “Coverage Tests”. This will create a basic test template (with links and advice for writing the various sections of the tests), and you can simply add the required test logic to the procedure body.

Figure 3

Having created this test procedure, run the tests again and you’ll see that the code coverage has increased from 0% to 1.51%. Our single code coverage test passed, but coverage is calculated regardless of whether the tests pass or fail; the first figure we see is an overall percentage of statements within the code that are covered with a test. This is useful as a quick measure of the thoroughness of our tests; ideally our tests would exercise every statement at least once and this figure would be 100%.

We can now drill into individual database objects to see to what extent each one is covered, and we find that the test we wrote covered the whole of the function dbo.ufnLeadingZeros. We haven’t yet conclusively demonstrated that the behavior of the function is correct, as we haven’t investigated passing potentially problematic values to it, but we do now know that the ‘happy path’ through the function makes use of every statement.

Figure 4

Now let’s add another test, this time for the function dbo.ufnGetStock. Again, we’re just writing a test that covers a simple successful case.

Figure 5

This time, our code coverage total has gone up again, but we haven’t achieved 100% coverage of the ufnGetStock function:

Figure 6

We can investigate further by clicking the function name, and SQL Cover will present us with the function definition, highlighted.

Figure 7

Any statements highlighted in green were executed at some point during the test run. Any un-highlighted statements were not executed. In this case, the SET @ret = 0 statement isn’t highlighted, indicating that we never checked the case where there was no matching product inventory record for the product ID.

Just because a statement isn’t covered does not mean that the code is buggy; we would only learn that from a test failure. However, it does mean that we could introduce a bug into this function, in future, and be none the wiser. The function dbo.ufnGetStock includes some branching logic to guard against returning a NULL value when given an unrecognized input, but unless we test that this branch of the code logic is working correctly, we could return NULL when an invalid product ID is passed in, and this might have unexpected consequences elsewhere. This is our cue to add another test.

Figure 8

Now when we execute our tests, we see that the whole function is covered, which will give us the confidence that every branch has been explored at least once.

Figure 9

We can continue this process of running our tests, and adding more each time we find uncovered statements, until we have a comprehensive set of tests. Moreover, our functions and stored procedures are likely to change as business requirements evolve, and making SQL Cover part of a regular deployment process will alert you to untested code being introduced in the future.


Code coverage is not infallible; it cannot tell you whether your code is really “doing the right thing”. However, it is an extremely useful tool for making sure your tests are comprehensive, and it provides an extra level of protection against bugs being introduced in the future.

Tools in this post

SQL Test

Write unit tests for SQL Server databases in SQL Server Management Studio.

Find out more

Share this post.

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

You may also like

  • Webinar

    How to keep your delivery processes secure with Database DevOps

    Extending DevOps practices to the database, brings additional advantages, ensuring you protect personal data across your SQL Server estate, while also improving the efficiency and quality of software delivery. We’ll explore the impact database DevOps has on regulatory and compliance requirements and how approaches such as automation, can improve accuracy, transparency, and faster recovery across the entire database development cycle.

  • Webinar

    How Financial Services can deliver database changes quicker while keeping data safe

    In Financial Services, where competition is fierce, and regulation is strict, there’s more pressure than ever before for you and your team to deliver value quick and keep data safe. But how do you increase the speed of your releases and continue delivering value to your customers while remaining compliant?

  • SQL Saturday

    SQL Saturday Gothenburg

    SQLSaturday is a free training event for Microsoft Data Platform professionals and those wanting to learn about SQL Server, Business Intelligence and Analytics. Redgate will be attending the event as a Silver Sponsor. Stop by our stand and meet the team.

  • Webinar

    DevOps, the database and the role of the DBA

    In this webinar, we discuss why the DBA is fundamental to DevOps success and the steps you can take to ensure database deployments can be made as frequently as the business demands, whilst also keeping your data safe.

  • Webinar

    Meltdown and Spectre: what’s the impact on your SQL Server performance?

    Join Bob Pusateri, Microsoft Certified Master (MCM) and Solutions Architect at Heraflux Technologies, as he delves into the scary world of processor vulnerabilities, brought to light recently by the Meltdown and Spectre bugs. Bob will explore the impact these bugs can have on your estate, from initial vulnerability through to performance after patching, and how you can keep on top of it all with SQL Server monitoring.

  • Webinar

    Data privacy & protection in the era of Connected Health

    Chris Unwin, Data Privacy & Protection Specialist at Redgate, hosts this webinar to discuss the biggest challenges that the healthcare sector faces regarding data compliance. He will look at the current level of data breaches in the sector, the cost of these breaches and provide you with strategies you can adopt to minimize your exposure to these threats.