DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations

Trending

  • Microservices Decoded: Unraveling the Benefits, Challenges, and Best Practices for APIs
  • VPN Architecture for Internal Networks
  • Design Patterns for Microservices: Ambassador, Anti-Corruption Layer, and Backends for Frontends
  • TDD vs. BDD: Choosing The Suitable Framework
  1. DZone
  2. Data Engineering
  3. Databases
  4. Effective Database Testing With SQL Test and SQL Cover

Effective Database Testing With SQL Test and SQL Cover

Code coverage is not infallible, but it is an extremely useful tool to make sure your tests are comprehensive and it provides an extra level of protection against future bugs.

Julia Hayward user avatar by
Julia Hayward
·
May. 07, 18 · Tutorial
Like (2)
Save
Tweet
Share
5.63K Views

Join the DZone community and get the full member experience.

Join For Free

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.

unit test Database sql Code coverage

Published at DZone with permission of Julia Hayward, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Trending

  • Microservices Decoded: Unraveling the Benefits, Challenges, and Best Practices for APIs
  • VPN Architecture for Internal Networks
  • Design Patterns for Microservices: Ambassador, Anti-Corruption Layer, and Backends for Frontends
  • TDD vs. BDD: Choosing The Suitable Framework

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: