Getting Started with Test Driven Design in SQL Server

Defects can be very expensive, particularly those in data access code. Any defect carries the cost associated with debugging the code to identify and fix the problem. Defects in database code can also cause additional unforeseen expenses because they might cause the data to become corrupt. The cost can explode quickly once that corrupted data is used to make business decisions. Just imagine an online store. If a defect causes changes to be randomly made to the quantity of items in orders, the online store will end up shipping too many or not enough items to customers. As a result, the store will have extra shipping costs as well as complaints from a lot of unhappy customers.

Related: T-SQL Best Practices, Part 1

I'll introduce you to a technique named Test Driven Design (TDD), which can help you identify and fix defects in T-SQL code. To understand TDD, though, you first need to know about some basic testing concepts.

Basic Testing Concepts

Good developers test their code before delivering it. Many times those tests are manually executed, which can take a lot of time and be prone to human error. To save time and prevent errors, you can use self-asserting tests. When a self-asserting test executes, it automatically asserts that the code performed the tested functionality to specification. The results clearly indicate whether the code worked or not. There are no error codes that can leave you guessing whether the result was expected or not.

If your tests are written in this way, they:

However, if you write all your test cases this way, you'll end up having to repeat a lot of code. The amount of duplication can be reduced significantly by using a testing framework.

For most languages outside of the SQL world, you would use a framework that follows the xUnit pattern, such as NUnit for .NET languages and JUnit for Java. To help with the development of well-designed tests, most of these frameworks have the following features:

Installation of tSQLt

The tSQLt unit-testing framework for T-SQL provides all the aforementioned features. Like most xUnit frameworks, tSQLt is licensed as open-source software. You can find more information about tSQLt as well as download it from the tSQLt - Database Unit Testing for SQL Server website. The download comes as a single .zip file that contains three .sql files and two .txt files. The .txt files contain the release notes and the license text. The framework itself lives in the tSQLt.class.sql file. It contains all SQL objects used by the framework, including the compiled assembly. You need to execute the tSQLt.class.sql file in every database for which you want to write tests.

The tSQLt framework uses some CLR functionality, so you must have CLR enabled. In addition, the database in which you want to install tSQLt needs to be marked as trusted. The SetClrEnabled.sql script does that for you.

One word of warning: tSQLt might cause unwanted side effects if executed in a production environment. Although the automatic transaction management of the framework is designed to prevent permanent alterations of the database, an accidental commit caused by a bug in the tested code could leave those changes behind. You also shouldn't set a production database to trustworthy, unless you have another reason to do so. Therefore, it's strongly recommended that you install tSQLt only in a development environment.

No matter whether you're working with Java, C#, or T-SQL, it's a good practice to have an independent development environment for each developer on the team. Preferably, you want to give each developer a separate SQL Server instance. If that isn't possible, each developer should at least have a separate set of all the databases that are used by your production application.

To follow along with the examples, install tSQLt into a newly created (empty) database. The tSQLt Quick Start Guide contains step-by-step instructions on the installation process. After the installation, you'll find a number of new objects in that database. There's the assembly tSQLtCLR and a user-defined type tSQLt.Private. Both the assembly and user-defined type aren't intended for direct interaction with your code, so you can ignore them. All other objects of the framework reside in the new schema tSQLt.

The First Test

To create your first test, you have to create a test class by calling the NewTestClass procedure. The syntax is:

EXEC tSQLt.NewTestClass 'DateFunctionTests'; 

This will create a test class named DateFunctionTests in the current database.

In object-oriented (OO) languages, tests are usually combined and organized into classes. The tSQLt framework borrows this concept. Under the covers, a test class is just a database schema. The NewTestClass procedure creates the schema and marks it as a test container. The word "New" indicates that it's executing a DROP statement followed by a CREATE statement. Every time you execute that file, the old tests get automatically cleaned out, so you're always starting from a defined state, without the chance of creating orphaned or lost tests. This makes it easy to combine all tests in one test class into a single file.

Although the name of this sample test class ends in Tests, there isn't a set naming convention for test classes. You can give them any name you like.

After you create the test class, it's time to create the first test using code like that in Listing 1. In tSQLt, every test is a stored procedure. This stored procedure needs to belong to a schema that was created using the tSQLt.NewTestClass procedure. It also needs to have a name that starts with test. Except for that requirement, there are no other naming restrictions if you use the bracket notation shown in callout A. This means you can use a long descriptive name and even include spaces and special characters. You'll never have to type that name again, so you can be as detailed as needed. That way, if a test fails, you'll be able to recognize and fix the problem quickly.

BEGIN CALLOUT ACREATE PROCEDURE DateFunctionTests.[test dbo.EndOfMonth  returns NULL if @DTime is NULL]-- END CALLOUT AASBEGIN-- The Assemble phase  DECLARE @Actual DATETIME;-- The Act phase  SET @Actual = dbo.EndOfMonth(NULL);-- The Assert phase  EXEC tSQLt.AssertEquals NULL,@Actual;END;

As the code in Listing 1 shows, every test consists of three basic phases: Assemble, Act, and Assert. During the Assemble phase, you set up the environment. In this example, only a variable is declared, but other tests will contain steps that insert data into underlying tables and perform other setup work required by the test.

In the Act phase, you execute the code that you're testing. In this example, the code is calling the dbo.EndOfMonth function, passing in a NULL.

In the Assert phase, you compare the expected and actual results. The tSQLt framework provides a few different Assert statements. In this example, the tSQLt.AssertEquals procedure is used to compare NULL (the expected value) to @Actual (the variable holding the result returned by the function).

In TDD, you start out by writing a single test to test a small amount of functionality in your system. The test is written before any production code is written. The next step is to execute the test. Even if you know that the test will fail, execute it anyway. This is an important part of the TDD process because it gives you immediate feedback about the test itself. Is it really failing? Is it failing for the expected reason? If anything unexpected happens, you can go back right away to fix the problem before it gets any bigger.

To execute all tests in the current database, you can use this statement:

EXEC tSQLt.RunAll; 

It's always a good idea to run all your tests after every change you make. This gives you instant feedback if the most recent change broke anything unrelated.

Executing the code in Listing 1 produces the output shown in Figure 1. The output consists of three parts. At the top, you'll see the output and errors that happened during the execution of the test. In the middle, you'll see the Test Execution Summary, which lists all tests that were executed and their results (i.e., Success, Failure, or Error). At the bottom is the Test Case Summary, which gives the counts for the number of tests that executed, succeeded, failed, and returned errors. The RAISERROR method is used to print this summary, so you can quickly check the outcome of your tests in the SQL Server Management Studio (SSMS) status bar. In this case, the status bar notes that the query contained errors.

Figure 1: Output from the First Test 

The output in Figure 1 points to the problem. The full error message is:

[DateFunctionTests].[test dbo.EndOfMonth returns NULL if @DTime is NULL] failed: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.EndOfMonth", or the name is ambiguous.{test dbo.EndOfMonth returns NULL if @DTime is NULL,5}

This result is expected. Because the test is the first piece of code you wrote, there's no dbo.EndOfMonth function yet.

Let's look at the test again and see what it tells you about the function you need to write. The test class is named DateFunctionTests, so you know that the test deals with dates. The test name mentions dbo.EndOfMonth. In the Act phase, a function with that name is called and it takes one parameter, but the parameter's name isn't given. Fortunately, the test name tells you that the parameter is named @DTime. The test name also tells you that the function should return NULL if a NULL is passed in. As you can see, this test name gives a lot of information about the function. If you follow TDD and use good names for your tests, the list of test names alone will give you a good understanding about what your system is doing. If anything is unclear, the test code can give you the missing details.

Again, descriptive test names can be a huge help in diagnosing failures. But you also need to make the test itself as readable as possible. Keep tests short, check only one thing in each test, and use descriptive variable names.

The first test failed because the dbo.EndOfMonth function was missing, so the next step is to add code that creates this function. Although the test name tells you the name of the required parameter, it doesn't tell you anything about the parameter's data type. Because you're dealing with a date-related function and the parameter's name is @DTime, selecting a datetime data type seems appropriate. The test stores the returned value in a datetime variable, so it makes sense to return a value of that data type as well. Here's the code to create the function:

CREATE FUNCTION dbo.EndOfMonth(@DTime datetime)RETURNS datetimeASBEGIN  RETURN NULL;END;

It's important to implement only what the test is asking for. Although this might seem extremely silly at times, it ensures that TDD is bringing you the most benefits. If you strictly implement only the functionality that a test is asking for, you'll end up with a test class that contains at least one test for every bit of implemented production functionality. This is very powerful, because it means that any unintended change is immediately uncovered by a failing test. For this to work, you need to follow the steps diligently and you need to execute all tests after every change.

After executing the code to create the function, execute the test again. You'll get output like that shown in Figure 2. Notice that the SSMS status bar now shows a check mark in a green circle, which lets you quickly know that all executed tests passed.

Figure 2: Output from the Modified First Test 

The Second Test

The first test passed, so it's time to write the next one, which Listing 2 shows.

CREATE PROCEDURE DateFunctionTests.[test dbo.EndOfMonthreturns the passed in date if it is the last day of a month]ASBEGIN-- Assemble  DECLARE @Actual datetime;  DECLARE @Expected datetime;-- Act  SET @Actual = dbo.EndOfMonth('2012-01-31');-- Assert  SET @Expected = '2012-01-31';  EXEC tSQLt.AssertEquals @Expected,@Actual;END;

The second test calls the dbo.EndOfMonth function, passing in a fixed date and expects the same date to be returned. Again, this is a small step, but it's important to keep your steps small. Figure 3 shows the results from executing the second test. The full error message is:

[DateFunctionTests].[test dbo.EndOfMonth returns the passed in date if it is the last day of a month] failed: Expected: but was:

Figure 3: Output from the Second Test

Once again, this error is expected because the return value of NULL is hard-coded into the function. To make this test pass, you need to look for the simplest solution. Both tests expect the passed-in value back, so you can directly return it, using the code:

CREATE FUNCTION dbo.EndOfMonth(@DTime datetime)RETURNS datetimeASBEGIN  RETURN @DTime;END;

(For brevity, this and the following examples omit the required DROP statement to remove the function before it's created again.) With this change in place, both tests will pass when executed.

The Third Test

The next test, which Listing 3 shows, forces some actual functionality into the dbo.EndOfMonth function. It requests that the function returns the date of the last day in January if the passed-in date is January 10. This matches the functionality that the name of this function suggested all along.

CREATE PROCEDURE DateFunctionTests.[test dbo.EndOfMonthreturns the last day of the month of the passed in parameter]ASBEGIN-- Assemble  DECLARE @Actual datetime;  DECLARE @Expected datetime;-- Act  SET @Actual = dbo.EndOfMonth('2012-01-10');-- Assert  SET @Expected = '2012-01-31';  EXEC tSQLt.AssertEquals @Expected,@Actual;END;

As expected, when you run all three tests, you'll see that the third test fails, while the other two tests are still passing. The error message for the new test is:

[DateFunctionTests].[test dbo.EndOfMonth returns the last day of the month of the passed in parameter] failed: Expected: but was:

To get this functionality implemented without breaking any of the other tests, you again need to take the smallest step possible without thinking about other problems that you might have to solve. In this case, you can use the code:

CREATE FUNCTION dbo.EndOfMonth(@DTime DATETIME)RETURNS DATETIMEASBEGIN  DECLARE @EndOfMonth DATETIME;  SET @EndOfMonth = DATEADD(MONTH,1,DATEADD(DAY,    -DATEPART(DAY,@DTime),@DTime));  RETURN @EndOfMonth;END;

This code looks fairly complex, but it really isn't. The inner DATEADD function returns the date of the end of the previous month. The outer DATEADD function then adds one month to that date to return the end of the current month. If you run all three tests again, you'll see that all of them will pass.

The Next Steps

If you happen to think about an important functionality you need to test or implement when you're running a test, write that thought down and then go back to the current test. That way, you won't get too distracted by the thought nor will you accidentally forget about it. For example, while implementing the third test, I jotted down a note about testing the behavior of the code when the time portion of the passed-in value isn't empty. Listing 4 shows the code for this test. Although including 00:00:00 in the expected value isn't technically necessary, it's a good idea to specify default values to aid readability. I'll leave making the last test pass for you as an exercise.

CREATE PROCEDURE DateFunctionTests.[test dbo.EndOfMonthreturns only datetime values with 00:00:00 time part]ASBEGIN--Assemble  DECLARE @Actual DATETIME;  DECLARE @Expected DATETIME;--Act  SET @Actual = dbo.EndOfMonth('2012-01-31 14:15:16.17');--Assert  SET @Expected = '2012-01-31 00:00:00';  EXEC tSQLt.AssertEquals @Expected,@Actual;END;

In looking at the last version of the EndOfMonth function, you might have noticed a big problem with its logic. You should try to identify this problem and write a test to point it out. You can find a solution to this mystery here: The Logic Problem in the EndOfMonth function. In brief, the problem has to do with the fact that different months have different lengths. There are a few patterns that help to write tests that would uncover a problem like this. See The Logic Problem in the EndOfMonth function for more details about some of those patterns.

The Takeaways

My goal here was to introduce you to TDD. The main concepts to take away are:

If you follow these guidelines, you'll have an easy way to execute tests that cover your implemented functionality. The repeated test executions will get you instant feedback if your last change had an unintended side effect.

If you're thinking that TDD seems like a lot of work, you aren't alone. In software development, most of the time is spent on finding and fixing bugs. Although TDD has a high upfront cost, it helps with finding a lot of those bugs early in the development cycle. The earlier a bug is identified, the easier (and therefore cheaper) it is to fix.

To learn more, go to tSQLt - Database Unit Testing for SQL Server. There you'll find more information about implementing TDD, the tSQLt documentation, and the latest tSQLt framework version to download.