Verifying Database Code by Using Unit Tests

You can use database unit tests to establish a baseline state for your database and then to verify any subsequent changes that you make to database objects. Before you can establish a baseline state, you must create a database project. You then create a test project and write sets of Transact-SQL tests that exercise your database objects. By using these tests, you can verify in your isolated development environment whether those objects are behaving correctly before you check them in to version control.

You can create tests that verify changes to any database object. In addition, you can automatically generate stubs of Transact-SQL code that test database functions, triggers, and stored procedures.


You can create and run database unit tests without having a database project open. However, if you want to auto-generate test scripts to test specific database objects from your project, you must open the database project that contains the objects that you want to test.

As you or your team members change the database schema, you can use these tests to verify whether the changes have broken existing functionality. You create database unit tests to complement the software unit tests that your software developers create. You must complete both sets of tests to verify the overall behavior of your application.

Your unit tests can verify that the procedures succeed when they are expected to succeed and that procedures fail when they are expected to fail. Testing that appropriate failures occur is referred to as negative testing.


You can create, modify, and run database unit tests in Visual Studio Premium and Visual Studio Ultimate. In Visual Studio 2010 Professional, you can run database unit tests but you cannot create or modify tests in the designer.

Common Tasks

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. This walkthrough includes an example of a negative database unit test.

Populate your database with test data: Before you can run tests against your database objects, you must have data in your database. The most common way to create test data is to use one or more data generators. You can also populate your database with reference data in the post-deployment script, or you can synchronize the data in your database with the data in another database.

Define database unit tests: You must create database unit tests in their own project. You configure the settings for that project and define one or more test conditions for each test.

Run database unit tests: After you define one or more unit tests, you run them, debug any problems, and examine your test results.

Manage groups of tests: You might organize tests into groups if they should usually be run at the same time. Test lists are still supported but for new groups of tests, you should instead consider test categories. For example, you might create a test category for the tests for your triggers or for all objects in a particular schema.

Check your test projects and tests in to version control: After you run your tests and verify whether they work correctly, you should check your test project and all associated files in to version control so that all members of your team can run your tests.

Define custom test conditions: You can create custom test conditions if you must test for behavior that the default set of test conditions does not cover. You must distribute these conditions to all members of your team who want to run the tests that use the new conditions.

Update existing unit tests: If you have unit tests that were created in a previous version of Visual Studio, you must upgrade them before they will build and run successfully with this release.

If you open a solution that contains both a database project and a database unit test project from a previous version of Visual Studio, you will be prompted to upgrade those projects. If you open a solution that only contains a database unit test project, you must upgrade that project manually.

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

  • Starting Team Database Development
    Before you can test a database, you must first create a database project that contains the offline representation of the objects and settings for the database.

  • Writing and Changing Database Code
    After you establish a baseline state for your database, you perform iterative development tasks such as defining and updating database objects. After you verify the changes, you share them with your team.

  • Build and Deploy Databases to a Staging or Production Environment
    After you implement and test all database code in isolated development and testing environments, the database administrator (DBA) deploys those changes into the staging and production environments.