Verifying Existing Database Code with 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 (T-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 T-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 generate T-SQL scripts to test specific database objects, 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.
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. |
|
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. |
Generate 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. |
|
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. For example, you might create a test list that contains the tests for your triggers or for all objects in a particular schema. Such groups are called test lists. |
|
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. |
How to: Add a Project or Solution to Version Control |
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 Database Edition, you must upgrade them before they will build and run successfully with this release. |
How to: Upgrade Database Unit Tests from Previous Releases of Database Edition |
Troubleshoot problems: You can learn more about how to troubleshoot common problems with database unit testing. |
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.Performing Iterative Database Development
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.