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.

Note

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.

Important

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.	Walkthrough: Creating and Running a 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.	Generating Test Data for Databases by Using Data Generators Add Reference Data to Tables When You Deploy the Database Compare and Synchronize Data in One or More Tables with Data in a Reference 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.	Creating and Defining Database Unit Tests Using Test Conditions in Database Unit Tests
Run database unit tests: After you define one or more unit tests, you run them, debug any problems, and examine your test results.	Running Database Unit Tests
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.	Defining Test Categories to Group Your Tests Defining Test Lists to Group Your Tests
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.	Add Files to Version Control Using the Check In and Pending Changes Windows Check In Pending Changes
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.	Define Custom Conditions for Database Unit Tests
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. Note 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.	How to: Upgrade Database Unit Tests from Previous Releases of Visual Studio
Troubleshoot problems: You can learn more about how to troubleshoot common problems with database unit testing.	Troubleshooting Database Unit Testing Issues

Related Scenarios

• 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.