Overview of Database Unit Testing
You can use database unit tests to establish a baseline state for your database and then to verify any subsequent changes you make to database objects. The first step is to write sets of T-SQL tests that exercise a certain database object and verify that the database object is functioning correctly. Then, as you change the underlying schema, you use these tests to verify that the changes have not broken existing functionality. By writing unit tests that evaluate the success or failure of database design changes, you can ensure that these changes are applied as expected and without introducing errors. Database unit tests are complimentary to the software unit tests created by your software developers. Both sets of unit tests are required to manage change to the application overall.
To write unit tests, you first need to create an isolated database development environment that is separate from your production database. Using Visual Studio Team System Database Edition, you follow these basic steps:
Create a database project
Import the schema from your production database to that project
Deploy the database project to create a database that uses the same schema as the production database
Populate that database with test data
After the isolated database development environment is established and populated with data, you can then start writing unit tests to evaluate the database objects in that environment. When the database objects have been tested, these unit tests can be used to detect regressions in functionality because of changes in the database. By testing these changes in an isolated development environment, you ensure that your production database is not compromised. After you have completely tested your changes, you can deploy the unit tests back into a larger test environment that contains other team member's changes, where they will enable you to quickly identify and correct regressions caused by additional database changes.
You might also run unit tests as part of the manual deployment process. In this case, you would generate a build script from a labeled set of sources, edit the build script as needed. Deploy the database to a staging server, and then run the database and software unit tests to verify the database and overall application state. After all tests pass, you can then deploy the database back into the production environment. For more information, see Managing Database Change.
You can create database unit tests that evaluate changes to any database object however, Database Edition includes some additional support for database functions, triggers and stored procedures. When you create a test for these objects, T-SQL code stubs to test the database object are automatically generated for you to customize. For more information, see How to: Create Database Unit Tests for Functions, Triggers, and Stored Procedures and How to: Create an Empty Database Unit Test.
You can create and run database unit tests without having a database project open. However, you must have the database project that contains the objects you want to test open to generate T-SQL scripts from these objects.
Like other test types built into Visual Studio Team System Test Edition, database unit tests are also integrated into Visual Studio. Using the Database Unit Test Designer, you can define Transact-SQL (T-SQL) test scripts that execute database scripts and then evaluate the results of those scripts against test conditions that indicate whether the script action succeeded or failed.
The Database Unit Test Designer contains the following features:
Navigation Bar - Test Name list box
Select individual unit tests in the test project by name or select Common scripts to develop scripts that run before or after each unit test in the same test class.
Navigation Bar - Test list box
Select the Pre-test, Test, or Post-Test options to write T-SQL scripts and test conditions that are evaluated at different times during the unit test. You can use the pre-test script to establish a certain state within the development database. You can use the post test script to return the database to the starting state after the test runs, or depending on the permissions that are required, use it to evaluate the success of the test.
Use this editor to write the pre-test, test, and post-test T-SQL script that are used in your unit test. Using the T-SQL RAISERROR command, you can also write T-SQL assert statements in your test script instead of using test conditions. Like test conditions, this command can also be used to indicate whether the test passes or fails based on the severity value. For more information, see Using T-SQL Assertions in Database Unit Tests.
Test Conditions pane
Use the test conditions in this window to evaluate the results of the T-SQL script execution.
The Test Conditions pane provides a set of pre-defined test conditions to test things such as the number of rows returned in a result set, the amount of time that is required to execute the T-SQL script, and whether a result set is empty. Each pre-defined test condition has one Assert method (defined on the base TestCondition class) that is called when evaluating a test condition. If a test conditions fails when evaluating the execution results against the desired results, the condition throws an exception and a fail result is returned in the Test Results window. Test conditions indicate that the test passed by not throwing an exception.
When you run the test, the T-SQL script is run first and returns a result. Typically, this is a result set. The implementation of the test condition determines what part of the returned information is evaluated for success. For example, the Execution Time test condition evaluates the time that is required to execute the T-SQL script and the Scalar Value test condition evaluates a value in a particular row. A pass or fail indicator is displayed in the Test Results window depending on the outcome of this evaluation. The pass/fail indicator depends on the successful evaluation of all test conditions for the test script. If one condition throws an exception, the test as a whole fails.
There are six pre-defined test conditions, but because test conditions are extensible, you can add others to the test conditions list, as needed. You can also use the T-SQL RAISERROR function within the T-SQL script to produce a pass or fail result and have this appear in the Test Results window. For more information, see Using T-SQL Assertions in Database Unit Tests.
An Inconclusive test condition is automatically added to the unit tests that you generate in Test Edition and the database unit tests that you generate in Database Edition. Tests are marked as "inconclusive" if this test condition is evaluated. The inconclusive test condition throws an InconclusiveException. By default, this test condition is included in all tests to remind you to add other asserts or test conditions. You can easily remove this test condition by selecting it in the Test Conditions window and clicking the Delete Test Condition icon.
For a complete list of pre-defined test conditions, see Using Test Conditions in Database Unit Tests.
Tests can also fail if they time out or are aborted. For more information about results that you might see in the Test Results window, see Basic Test Results.
You can use the data generation feature of Team Edition for Database Professionals to add test data to your database environment. This feature includes a group of data generators that will produce the same data given a particular seed value. This enables you to generate the data, inspect the generated database to determine the results that various tests should produce and then write your tests accordingly. For more information about how to use data generation with unit testing, see Walkthrough: Creating and Running a Database Unit Test. For more information about the different data generators, see Overview of Generating Data.
You can run database unit tests within the IDE or at a command prompt if you want to automate test runs. For more information, see Command-Line Test Execution.