Unit Testing

You have gotten your next task, and you are ready to start writing some T-SQL. You also want to implement functionally testing your code. When you are testing a single piece of code, this is called unit testing. Understanding what unit tests are and why you should use them can help improve your T-SQL code quality. Once you know what unit tests are, you will want to work toward learning when unit testing is beneficial. This will get you ready to start writing your first unit tests for SQL Server.

The concept of unit testing is basic. You have a single change that you want to make to your T-SQL code. Unit testing allows you to test and confirm the functionality for a single piece of code. One of the common methods with unit testing is to create a scenario that fails, write the T-SQL code, run the unit test, and repeat until the unit test passes. This is test-driven design. Whether you are implementing new functionality or resolving a bug in T-SQL, unit tests can help verify your code.

When creating unit tests, you will need to think differently than you normally would when you set out to write T-SQL code. We are creating unit tests to confirm that the code we are writing works as expected. In order to verify that your T-SQL code is working as expected, you will want your unit test to indicate a Pass. When creating your unit test, your first execution of the unit test should Fail. Starting with a failing unit test would indicate that the desired functionality does not exist.

There are many different implementations of running unit tests. Unit tests can be written inside of Visual Studio. You can also use a free third-party unit testing framework that creates database objects in T-SQL. Another option is using a paid third-party tool to create and run your unit tests. There are benefits and drawbacks to each of these options.

Another factor in determining how you will implement unit testing is in how you would like to run your unit tests. Depending on your environment, you may decide that you would prefer to manually run your unit tests. However, you may also decide that you want an automated way to run your unit tests. The easiest method that has the least amount of overhead is manually running your unit tests. This can either be running these tests with a stored procedure or through a GUI.

You can use unit tests to verify just about anything with your database schema. Most of those unit tests will be for testing functionality for your database objects like stored procedures, views, and functions. I may be biased, but I hope you have far more stored procedures than views or queries. Do not be concerned if you do not have any unit tests in place now. You can create unit tests as you need them. This may mean that you do not have complete code coverage for your unit tests, but it prevents you from spending time implementing unit tests for code that is not modified frequently.

You can see in Table 11-2 that both active and inactive recipes are returned when executing the stored procedure. In this way, we have created a basic unit test, the query in Listing 11-1, and confirmed that this test failed when running the stored procedure in Listing 11-2 since the inactive records were also returned with the result set.

We have confirmed that the changes we made to Listing 11-3 have correctly removed the inactive recipes from the result set.

This method of testing your code is a good place to start, but it can be difficult to follow consistently. You may have some tables that can store data in various states. For instance, if I wanted to find the information about the frequency with which a recipe was prepared based on an ingredient on sale, the logic to test the results gets more complex. That is where the benefit of using third-party tools can be helpful.

Finding individual sample sets for your data can be time consuming and error prone. In addition, you may spend more time trying to find the right test case rather than writing or testing the T-SQL code. Another challenge is that you skip over important test cases due to the time and energy spent manually finding test data that already exists in the database. However, using T-SQL to find specific test cases in your data is not your only option. You could manually insert test data and perform your tests on the records that have been inserted. This method would allow you to determine what were the best scenarios for the code you were testing. In most cases, this sample data would not remain in a centralized location. You may still need to rewrite specific examples each time you test a new piece of functionality.

There are different types of unit testing tools available to you. Unit testing can be handled by adding additional T-SQL objects to your database. Another option is to pay for third-party tools that can help manage your unit tests. Another option is to use Visual Studio to create and manage unit tests. While all these options are valid and have their own benefits, I want to focus on what you can accomplish by creating unit tests natively in Visual Studio. Figure 11-1 shows the first step to create a unit test in Visual Studio.

Once you select the option to create a unit test, Visual Studio will walk you through the process of creating your first unit test. A dialog box will open as shown in Figure 11-2.

The top portion of the window allows me to select which objects should be used to create unit tests. For this example, I will work on creating a new unit test for the dbo.GetRecipe stored procedure. I am also creating my first unit test associated with this database project. I can select that I want to create a new Visual C# test project. However, you do not need to know C# to start creating your own unit tests in Visual Studio. I have given this new project a name and decided to create a new class. I can reuse this class for other unit tests going forward.

Once I select OK on the dialog window, I will get another pop-up as shown in Figure 11-3.

There are a couple of options that can be set here. Most of this has to do with selecting a data source we are using where we can perform the unit tests. I have selected the pre-existing Menu database to run the unit tests. I have the option to automatically deploy the database before running the unit tests. However, I would prefer to only deploy the database if the unit tests passes.

Once I have set the connection string and configured the additional settings, Visual Studio will add a new project to the existing solution. You can see in Figure 11-4 how your database project will look along with the unit testing project.

In Figure 11-4, none of the objects in the original MenuDB project have been altered. You can see all the objects created for the unit testing project are available to be checked into source control. After the unit testing project has been created, some additional windows will open in Visual Studio. One of these windows allows you to set test conditions. By default, a test condition will be configured for Inconclusive. You can leave this test condition or remove it. In Figure 11-5, I have selected the drop-down of available test conditions.

I am still trying to create a unit test to verify that inactive recipes are not being returned by the dbo.GetRecipe stored procedure. Like the manual unit test I wrote previously, I am going to select the condition of Empty ResultSet. You can see this test condition in Figure 11-6.

Now that I’ve created a test condition, I need to write some T-SQL code for this unit test. I have selected Empty ResultSet as my test condition. For this unit test to pass when it is run, the T-SQL code inside the unit test must not return any results.

I first create a table variable. The next section of the unit test inserts the results from the stored procedure dbo.GetRecipe into the table variable. The final step is to select only the inactive records from the table variable. Once I add the preceding code to MenuDBSQLServerUnitTest.cs and save the file, I am ready to run my first unit test. You can go to the Test menu to run the unit tests as shown in Figure 11-7.

I am using test-driven design for my unit tests, and I am testing this unit test before changing the original stored procedure. For this scenario, I am expecting the unit test to fail. You can see the results of running this unit test in Figure 11-8.

The original stored procedure still returns inactive recipes. This caused the unit test to fail. Once I updated the stored procedure dbo.GetRecipe with the T-SQL code from Listing 11-3 and repeat the unit test, I will get the results in Figure 11-9.

Now that the unit test has run successfully, I can be confident that my new T-SQL code is working as intended. There are other unit testing options available for database projects. I propose you investigate several alternatives and collaborate with your coworkers to determine which method works best for your environment.

Integration Testing

While it is good to know that a single piece of database code is working as expected, most T-SQL code does not exist in isolation. The very nature of relational design infers that items in the database are related to one another. We normally think of the relationships between tables. However, stored procedures access data that exists in tables. If I wanted to verify an insert of data, I may run a unit test. This would confirm that the data I expected to be inserted has been inserted.

Issues can arise when there is more than one way to access the same data in the database. Oftentimes, these queries were written at different periods in time. This can cause the logic in the queries to be slightly different. I have also seen instances where different business units in a company have different calculations for the same data. If a calculation for one business unit is reused for another business unit, it can cause what appears to be inaccurate results.

This brings up a question as to how to keep our T-SQL code and query results consistent across multiple different database objects. This is where integration testing can be useful. Integration testing is the term used to indicate testing that is designed to work with more than a single piece of code. If you are using an application to insert data and you want to verify that the data is inserted correctly, that could be considered integration testing. You would be testing both the application’s ability to connect to SQL Server and the T-SQL code to insert the data into the database.

This is only one scenario where integration testing can be used. A common situation I find myself in is working with two stored procedures that are intended to pull back generally the same information. There are times that changes to one of the underlying tables or code in one of the stored procedures may cause these two stored procedures to return different results. Unfortunately, these differences in results are not usually found until well after the T-SQL code was deployed. This causes an overall loss of confidence in the applications.

While it is preferred that these intertwined stored procedures would be documented or better yet re-factored down to a single stored procedure, this is not always an option. There are costs associated with having multiple versions of similar code, but there are also costs with having multiple processes relying on the same code. For the rest of this chapter, we will assume that the T-SQL code cannot be rewritten to get rid of the dependency.

Just like unit testing database code, you can begin your integration testing manually by using T-SQL queries. The biggest factor in your integration is understanding how your environment works together. Sometimes this is found by accident like when something breaks. Other times, you may be a subject matter expert and already know the interactions. Either way, in order to begin integration testing, you first need to have at least two things to test.

You could begin by testing a data insert along with a data select of the same table(s). You could also use integration testing to compare the results of two queries. This could happen if you have one query where you return all values and another query where you search for a specific value. While these two stored procedures would not match for every record, they may have matching results for a specific record. This type of integration testing would confirm that the values returned in the columns were consistent between one another.

If I start working on making changes to a stored procedure that selects data, I may want to test the insert of the data along with the select for that same data. I may be working on updating the stored procedure dbo.GetRecipeAndIngredientByMealTypeID to only return active recipes. I can unit test this stored procedure by creating a unit test for inactive recipes and a unit test for active recipes. I can also use integration testing to confirm that this stored procedure will still return expected results after creating a new recipe. This type of testing may be considered trivial now, but as the application grows and matures, they may become increasingly useful. I have found that there are times the business needs answers faster than they can be provided. This sometimes causes database objects to be used in ways they were not originally intended.

When determining what you should be integration testing, think about any dependencies related to the T-SQL code that you are writing.

Some T-SQL code has a more obvious need for integration testing than others. One of the scenarios I have found that need the most integration testing involves different database objects that return the same data. This can be two different stored procedures. It is also possible that the integration testing can compare the results between functions and stored procedures or views and functions. While these database objects may return different columns in the result set or in a different order, the columns that are the same can be compared.

Other times you may have T-SQL code where one database object depends on the data handled in a previous step. You may have a stored procedure that updates a value in the table. A view or stored procedure may only return a specific subset of values. Using integration testing can allow you to execute the first stored procedure where you will update a lookup value in the table. Depending on the testing required, you can execute the stored procedure and confirm the record appears. Unless the record should no longer appear, then you can use integration testing to confirm that the record no longer appears.

A common situation where this can occur is when you want to soft delete or disable a data record from your application. You can use one set of T-SQL to disable the record. There may be one or more database objects that should then be tested to confirm that the disabled records no longer appear. Creating a way to keep all your integration testing scenarios together and making sure that they are repeatable is what will protect your applications in the future. Integration testing your code now confirms that your current version of T-SQL code will pass. However, automating and repeating your integration testing going forward will allow you to continue to verify that new bugs have not been introduced into your T-SQL code.

Depending on the design and complexity of your system, you may have data that is entered in one application and is sent to or used by another application. Throughout the business activities, this data may end up in different databases or different tables. This can involve different stages of data throughout your business. This can also include using integration testing between the transactional databases and a data warehouse. Using integration testing in this manner can help make sure that the data entered in your applications remains consistent when it is migrated into a data warehouse.

There are more graceful methods around that can help with integration testing. However, I have found that most of these reference unit testing. The only difference for using these tools with integration testing is how the tests are written. This means you can use the unit testing functionality within Visual Studio as shown in the preceding section. There are other tools you can use for your unit and integration testing, but they will not be covered in this book.

Load Testing

Another aspect of working with SQL Server is handling large data sets quickly. It is often easy to hope that T-SQL code that is functionally correct will also perform well. However, that is not always what happens. While we can use execution plans to get a good idea of the relative performance of a query, that does not guarantee that the code will perform well under a heavy load. If we want to get an idea of how the T-SQL code will perform under stress, we will need to perform load testing.

Load testing presents some very special issues. One challenge is that the hardware is often between the load testing environment and Production. In addition to hardware differences, there is usually a difference in the data that exists in the lower environments. This can be anything from having less data in lower environments to the data in the lower environments being cleansed and having different statistics. Additional differences can include data that has been entered in the lower environments that does not match Production. In many cases, these differences cannot be resolved.

Unless you have the exact hardware and the exact Production databases, your load testing will not match Production with complete certainty. It is still beneficial to try and load test your T-SQL code. Even if you are unable to create the perfect load testing environment, you can still compare relative performance of T-SQL code in your load testing environment. The next step is to figure out how to implement load testing. A simple but not very reliable method is to create T-SQL scripts to generate dummy load testing data. This method will give you a general idea of performance, but without significant analysis of existing Production data, it will not accurately reflect Production performance.

There are several third-party tools available for load testing and many of them are free. These tools should make it simpler to begin load testing. However, you are faced with the same issue that these tests may not accurately reflect Production activity. Another option is to use distributed replay to collect the transactions from Production and replay them in your lower environments. While implementing load testing is an important aspect when developing your T-SQL code, the steps needed to load test are outside the scope of this book.

Static Code Analysis

Creating standards for formatting and developing T-SQL coding standards is only a start when it comes to writing T-SQL code. Back in Chapter 3, I wrote about standardizing your T-SQL code. T-SQL Coding Standards were covered in Chapter 9. These standards are only useful if they are followed. Many times, the standards are lengthy and can be difficult to remember. There are better ways to ensure these standards are followed than trying to remember all the rules. Static code analysis can be used to confirm that your standards are being followed.

As discussed previously in this book, there are benefits to standardizing how you and your coworkers write T-SQL. This can make the code easier to read and save time debugging issues in the T-SQL code. Unfortunately, the benefits of standardization cannot be realized if the T-SQL code getting checked into source control does not match the formatting standards. This is one situation where static code analysis can help.

The static code analysis allows you to write T-SQL code. This code can be saved and checked into source control. Prior to deploying the database code, static code analysis is used to verify that the checked in T-SQL code meets the coding standards. There are options to enforce T-SQL formatting, but the main option that is used is a third-party tool.

In addition to using static code analysis for standardization of formatting for database code, you can use static code analysis for your database coding standards as well. There is built-in functionality in Visual Studio 2017. You can find the window in Figure 11-10 by selecting the Code Analysis option from the Properties menu.

You can select code analysis to be run every time you build the database project. You also have the option of selecting which items should be included as part of your code analysis. These options include best practices and items that can help affect the performance of the T-SQL code. In addition, there is the option to escalate some of these rules to error and fail instead of sending a warning message.

The benefit of static code analysis is that it automates the process of ensuring that the T-SQL code is meeting your business’ coding standards. This can help code rejections feel less personal, and the code is being reviewed and rejected as part of the overall build process. The build process will also communicate the warning or error message in a consistent method.

Getting your database code into source control is only half of the issue. The real challenge can come when trying to deploy database changes that are only saved in source control. Determining how you want to deploy your code will help you determine what method to use to save your T-SQL code. Deploying T-SQL code from source control will be discussed further in Chapter 12.