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

  • Community event

    SQLSaturday Oslo

    SQLSaturday is a free 1-day training event for Microsoft Data Platform and SQL Server professionals, providing a variety of high-quality technical sessions. This is a great opportunity to network and learn from MVPs (Microsoft Most Valuable Professionals), local and international speakers. You can even win prizes from the sponsors – including Redgate – at the end of

  • Webinar

    Database DevOps in the era of Connected Health

    In this webinar, Technical Engineer Arneh Eskandari, will explore the key drivers for DevOps adoption in the healthcare sector and discuss how you can optimize your database deployment pipeline for compliance and release frequency.

  • Article

    Build and fill a database using JSON and SQL Change Automation

    Phil Factor demonstrates how to export data from a database, as JSON files, validate it using JSON Schema, then build a fresh development copy of the database using SQL Change Automation, and import all the test data from the JSON files.

  • Article

    Automating database deployments to and from source control using SQL Compare and PowerShell

    How to use SQL Compare Pro command line with PowerShell to automate the processes of getting a new database into source control, updating the source with the latest database changes, creating a new build script for a database version, and creating a deployment script to synchronize an existing database with source.

  • Forums

    SQL Test Forum

    Unit test add-in for SQL Server Management Studio