Product articles
SQL Test
Database Testing
Effective Database Testing with SQL…

6 April 2018
6 April 2018

Effective Database Testing with SQL Test and SQL Cover

Julia Hayward describes the basics of how SQL Test’s code coverage can help uncover the darker, untested paths in your database code.

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.

Summary

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

Unit test add-in for SQL Server Management Studio

Find out more

You may also like

  • Article

    Picking over the Bones of a SQL Injection Attack

    The best way to learn how to protect your databases from SQL Injection is to to see it in action and confront its consequences. This article tells the story of an attack on a vulnerable SQL Server REST interface, explaining how the attack unfolds, the mistakes that made it possible, and SQL Monitor's role as the 'canary in the mine'.

  • Article

    Using SQL Compare with Row Level Security

    As you test your row-level security in various environments, you can use SQL Compare to script out and deploy those changes to other databases.

  • Article

    Monitoring Distributed SQL Servers using SQL Monitor

    If your SQL Server estate is large, or installed across different, isolated networks, or both, then you need a distributed monitoring solution. It is very easy to set up multiple base monitors in SQL Monitor, and then to manage the monitoring, alerting and troubleshooting for your entire SQL Server estate, from a single web interface.

  • Article

    How to compare two DacPacs using SQL Compare

    Feodor shows how to use Data-Tier Application Packages (DacPacs), together with the Data-Tier Application Framework (DacFx), as an alternative way of scripting out a database, and deploying database changes, in cases where SQL Compare can't get direct access to the production database.

  • Forums

    SQL Test Forum

    Unit test add-in for SQL Server Management Studio