Software QA FYI - SQAFYI

SQL Server Unit Testing with tSQLt

By: Sebastian Meine and Dennis Lloyd

Consider the last few database bugs you had to fix: Were they easy to find? When you fixed them, how sure were you that something else didn't break? Did it result in an incorrect value being recorded? Was the user presented with erroneous data? If so, were they aware of it at the time, or were they unaware and it resulted in a potentially poor (or even dangerous) decision?

SQL code controls how data is retrieved, stored and maintained in many applications. It includes views, stored procedures, functions, triggers, the creation of tables and the relationships between them, and query statements embedded in other programming languages. Writing this code often involves decisions about the nature of data being processed, complicated joining and filtering to retrieve or modify the correct data, performance tuning, data cleansing, replication and data maintenance. In far too many cases, the testing performed on this code is insufficient to prevent serious defects.

Much of the testing done on SQL code today involves manually executing the code on a copy of production data and hand checking the results. Manual testing results in a laborious process and some test cases being forgotten as the code changes. Using existing data means that the code is not tested against cases that are not currently in the system, but may be tomorrow. Hand checking the results introduces subjectivity and human error to the process.

Sophisticated database developers have tried their hand with the unit testing tools available for SQL testing. However, many abandon their efforts as test case maintenance using these tools becomes prohibitively expensive. There are two primary reasons for this expense: making changes to the single set of data used to populate the test database causes test cases to break; or the test cases break as the structure of the database changes.

Many attempts at database unit testing prescribe a pre-populated test database which includes the record required by the tests. If a new test case is written that requires data that is not in the test database the existing data must be augmented. It does not take long before augmenting data for a new test case causes several other test cases relying on the same data set to break. Furthermore, some test cases become impossible to write with the existing test data. For example, a test case which tests the result of a query when data does not exist with certain criteria.

The solution to the problem of data sharing is to have each test case create its own set of test data. Each test case assumes an empty or nearly empty database. At the beginning of the test, the test case inserts all of the data required for the test case to execute. In order to satisfy constraints, data will often need to be inserted into columns and tables which are unrelated to the code being tested. As the number of test cases grows, there will be more places that must be updated if a column is modified or a table with a foreign key constraint is added.

The tSQLt unit testing framework for SQL Server was created to address these problems. It allows each test case to create the necessary data, while eliminating the pain of test case maintenance due to unrelated schema changes. It is a freely available, open-source project which can be downloaded from:
A Simple Test Case
Let's walk through some examples of unit test cases written in tSQLt to look at how it solves these problems. Imagine we have an application that accepted user-entered input for daily temperature data, and recorded it in a table. The application started off rather informally so no restrictions were put on the data the user could enter, but now we need to make serious use of the data so we need to interpret consistently. Here's an example of some of the data in the DailyWeatherInfo table:

Full article...

Other Resource

... to read more articles, visit

SQL Server Unit Testing with tSQLt