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 that 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.