WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
Unit Testing is now a fixture on the Microsoft Visual Studio landscape. Almost every type of application development has some sort of specialized Unit Testing feature built into Visual Studio. Even SQL Server database development has Unit Testing.
In an earlier article, I introduced the Visual Studio Team System Database edition http://www.developer.com/db/article.php/10920_3751801. In this article, I'm going to introduce you to database Unit Testing project built into Visual Studio Team System.
Unit Testing a Database?
You may be wondering, why Unit Test a Database; isn't this overkill? For a trivial database, yes Unit Testing may be overkill, just like unit testing a trivial application may be overkill. So, though a database may be developed different from, for example, a desktop application, you Unit Test for the same reasons you Unit Test any other application.
In particular, you Unit Test to exercise all your code and verify that the relationships among all parts of your code, at present, and in the future; adhere to what you originally intended. Here are some sample scenarios.
Often, a developer needs to assemble some type of scaffolding to test a stored procedure or view. Unit Tests can be the basis of the scaffolding. Unlike sample applications written by the original developer and then discarded, Unit Tests can be checked in with the developed code and utilized by other developers.
Unit Tests can be configured with a whole range of data sizes and shapes, at the very edges of what is allowed in the database. An application may not exceed size limits in a database until it is ported to, for example, a new language. Often, a developer will not be aware that a stored procedure parameter is bigger than a particular field it is writing to a table until the upper limits are reached.
Unit Tests serve as living documentation and may answer questions like: How you intended to use a Stored Procedure? and How fast should a particular query execute?
Building on the AdventureWorks sample project from my prior article, I'll unit test a modified version of the uspLogError stored procedure.The code appears in Figure 1.
--Removed the error check IF @dbVersion = '9.04.10.13.00' BEGIN IF ERROR_NUMBER() IS NULL RETURN; END ... INSERT [dbo].[ErrorLog] ( [UserName], [ErrorNumber], [ErrorSeverity], [ErrorState], [ErrorProcedure], [ErrorLine], [ErrorMessage], [DBVersion] ) VALUES ( CONVERT(sysname, CURRENT_USER), ISNULL(ERROR_NUMBER(),0), --Added ISNULL check for error ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), ISNULL(ERROR_MESSAGE(),''), --Added ISNULL check for error @dbVersion );
Figure 1: uspLogError changes
I commented out the ERROR_NUMBER() check, allowing the code to continue execution with or without an error. I also modified the Error Log to include the Database Version in the error log code.
Adding a Unit Testing Project
Adding a test project is straightforward; simply add a project to the solution. Select a Test Project from the Project templates dialog (see Figure 2).
Figure 2: Create Project dialog
Aside from adding the project to your solution, Visual Studio adds some additional solution components separate from the project (see Figure 3).
Figure 3: Additional solution components
The files control your testing experience and allow you to configure more complicated testing scenarios. Neither of these files will be part of this articles demonstration, but you can find more details in the resources at the end of the article.
The test project appears in Figure 4.
Figure 4: Sample Project components
The project is created with a default Manual Unit Test and a blank Automated C# Unit Test. These files can be deleted, but I'm leaving them in so you can see how tests are grouped and execute alongside each other later in the article. The AuthoringTests.txt file explains how to navigate the project and how to configure some of the Unit Testing options.
As you can see, I've created a Test project for Database Unit testing, but there are no Database tests in the project, so I'm going to add one and configure it to test my TSQL code.
Database Unit Test
Like all Visual Studio projects, you right-click on the project and select "Add Test" or "Add Item" to create new items in the project. The dialog below appears when you select "Add Test" (see Figure 5).
Figure 5: Add New Test
Because I'm creating the first Database Unit Test in the project, I'll be prompted to set database configuration information for the whole project. You must at least select a database connection to configure a project. As you can see in Figure 6, for simplicity, I selected the database maintained by the AdventureWorks database project. Therefore, as I change and edit the project the underlying database I'm using in the unit test will also change.
Figure 6: Set database configuration
A database unit test can be quite sophisticated. Later in the article, I'll show you how to include data in your test. More advanced testing scenarios, though, are beyond the scope of this article.
Once you complete the configuration information the Database Unit test will look much like the test shown in Figure 7.
Figure 7: Empty Database Unit Test
Like Unit Testing with other types of projects, you write Database Unit Tests in the same language you use for development. In the Database Unit Test, the development language is TSQL.
Most likely, you'll need to operate on some data to execute your tests. So, before I develop the Unit Test, I'm going to create some sample data and later incorporate the data in the Unit Test.