Last month, in "Explosive Source Control" (http://www.sqlmag.com, InstantDoc ID 93209), I explained why you should place your database schema under source control and how to do so in Visual Studio Team Edition for Database Professionals (Team Data). In that article, I introduced the idea of creating a baseline, a process in which placing schema under source control is only the first step. This month, I explain how Team Data helps you accomplish the second step in creating a baseline: creating a set of baseline tests for the database. Team Data provides a full framework for creating database unit tests that you can use to validate both your existing database objects and any changes that users make.
Much of Team Data's purpose is to help you manage changes to your database schema, control which changes occur, make the changes, and ensure that the changes are successful. Team Data's test framework is basically the same as Visual Studio Team Edition for Software Testers (Team Test), which lets you write tests in managed code. However, Team Data extends this framework by adding a new designer that lets you write tests in T-SQL. This new designer lets database professionals create tests in their preferred, and probably more appropriate, language and lets experienced managed code developers delve under the covers and use managed code to write more complex tests.
New Test Generation
The process of creating a unit test requires little more than a few mouse clicks. First, you must create a test project. To do so, start in a Visual Studio database project (which I described in "Explosive Source Control"). You can add a unit test to the database project by choosing database test as the test type from the New Test… menu item on the Test menu. Or, more simply, you can add a unit test by selecting any of the stored procedures in the database project’s Schema View, right-clicking, and selecting the Create Unit Tests… menu item. After completing these steps, Team Data creates the test project and adds the first test to the project.
If you choose to create a test based on a stored procedure, Team Data creates a basic T-SQL test based on the inputs and outputs of that stored procedure. (If you choose to select database test from the right-click menu, Team Data generates a blank input form on which to build a test.)
From this point on, you can adjust the T-SQL code to perform any type of test you want. But to simplify the basic test-creation process, the test project provides a set of test criteria that you can run. In the test project, select these criteria from the Test Conditions pane. The criteria consist of pre-defined test conditions, including RowCount, which lets you test the number of rows returned; ExecutionTime, which lets you define how quickly the test should run; NotEmptyResult Set, which lets you make sure the test always returns some value; and many other basic conditions.
Once you have a test project, you can continue to add tests to check various aspects of the database. These tests can be as simple or complex as you want. But while you're creating a baseline, make sure your tests cover all the major schema objects so that as you make database changes, you can catch any regression from the original baseline.
See Results
Tests are executed from the Test View window, which Web Figure 1 shows. (If the Test View window is closed, you can open it from the View menu.) In this window, you can choose to run an individual test or run all the tests in the list that you see in the window. The Test Results pane at the bottom of the IDE shows whether the test succeeded. One great advantage of the test infrastructure implementation in Team Data is that you also have Team Test, which lets you integrate the database unit tests into the All Up test infrastructure and manage them from the Test Explorer. You can run database unit tests as part of your All Up test infrastructure so that the All Up, nightly, or batch test runs catch database changes as they occur and ensure that problems can be streamlined back to the project.
In addition to having a good set of unit tests, another important aspect of database testing is having good quality test data. In next month's column, I'll look at the new Database Generator functionality, which lets you create meaningful and repeatable test data without having to use real production data.
