This documentation is archived and is not being maintained.

Creating and Defining Database Unit Tests

You can run database unit tests to verify whether changes to one or more database objects in a schema have broken existing functionality in a database application. These tests complement the unit tests that your software developers create. You must run both kinds of tests to verify the behavior of your application.

You can verify the behavior of any object in your schema by creating an empty test and adding custom code in Visual Basic or Visual C#. As an alternative, you can automatically generate a stub of a Transact-SQL script if you want to verify the behavior of a particular function, trigger, or stored procedure. After you generate the stub, you must customize it to obtain meaningful results. 


You can create an empty test, add code to it, and run it without having a database project open. However, you cannot automatically generate a Transact-SQL stub that tests a function, trigger, or stored procedure without opening the project that contains the object that you want to test.

In the following table, you can find descriptions of common tasks that support this scenario and links to more information about how you can successfully complete those tasks.

Common Tasks

Supporting Content

Get hands-on practice: You can follow an introductory walkthrough to become familiar with how to create and run a simple database unit test.

Learn more about database unit tests: You can learn more about the files and scripts that compose a database unit test. You can also learn about how to use test conditions and Transact-SQL assertions in your unit tests.

Create one or more test projects: You must create database unit tests in a test project. If you create a database unit test before you create a test project, a test project is created for you. You can create the test projects first if, for example, you want to use different data generation plans or different deployment configurations in different sets of tests. When you create the test project, you can configure test settings (such as the connection string), deployment settings, and a data generation plan to use for that project.

Configure how the unit test is run: You can specify the connection string to the database against which you run the tests, the data generation plan, and deployment settings. You first configure these settings when you create the test project, but you can also modify them later.

Create a database unit test: You can automatically create Transact-SQL code stubs for database unit tests that verify the behavior of a function, a trigger, or a stored procedure. You can also create an empty database unit test and add code later to test other types of database objects.

Write code for a database unit test: After you create a unit test, you write Transact-SQL code to test a database object. For each test, you define one or more test conditions that determine whether the test will pass or fail. For more complex tests, you can modify the Visual Basic or Visual C# code in the database project. For example, you can write a unit test that runs in the scope of a single transaction.

Troubleshoot problems: You can learn more about how to troubleshoot common problems with database unit testing.

Generating Test Data for Databases by Using Data Generators

Before you run a database unit test, you might want to insert representative data into your database. You can use a data generator to create realistic test data without exposing production data to your developers.

Running Database Unit Tests

After you create your database unit tests, you can run them from the Test View window, the Database Unit Test Designer, or by using Team Foundation Build.

Define Custom Conditions for Database Unit Tests

You can create a custom test condition to test a behavior that the default test conditions cannot verify.