Create and Define 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 (T-SQL) 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 T-SQL stub that tests a function, trigger, or stored procedure without opening the project that contains the object that you want to test.

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.

Walkthrough: Creating and Running a 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 T-SQL assertions in your unit tests.

Scripts in Database Unit Tests

Database Unit Test Files

Using Test Conditions in Database Unit Tests

Using Transact-SQL Assertions in Database 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.

How to: Create a Test Project for Database Unit Testing

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.

How to: Configure Database Unit Test Execution

Overview of Connection Strings and Permissions

Create a database unit test: You can automatically create T-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.

How to: Create Database Unit Tests for Functions, Triggers, and Stored Procedures

How to: Create an Empty Database Unit Test

Write code for a database unit test: After you create a unit test, you write T-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.

How to: Open a Database Unit Test to Edit

How to: Add Test Conditions to Database Unit Tests

How to: Write a Database Unit Test that Runs within the Scope of a Single Transaction

Keyboard Shortcuts for Database Unit Test Designer

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

Troubleshooting Database Unit Testing Issues

See Also


Terminology Overview of Database Edition