Getting Started with SQL Server Database Unit Testing in SSDT

SSDT now enables you to develop, debug and execute database unit tests interactively in Visual Studio, which can then be run from the command line or from a build machine, for example, a Team Foundation Build server configured for continuous integration. SSDT helps put database testing on an equal footing with other aspects of application testing, helping you raise and then maintain the quality of your SQL Server database applications. This post provides a quick introduction to SQL Server database unit testing with SSDT. For a more detailed information and a more comprehensive walkthrough available see Verifying Database Code by Using SQL Server Unit Tests in MSDN.

Unit testing SQL Server databases with SSDT is very straightforward, although there are a couple of things to look out for. First you must have Visual Studio 2010 or 2012 Professional edition or higher installed. If you have Visual Studio 2010 Premium or Ultimate edition installed then the new SQL Server database unit testing installs alongside the existing database unit testing from Visual Studio. You can continue to use the old tools but you cannot mix database unit tests created by both versions in the same test project - more on this below.   

Test Projects and converting existing Test Projects

SQL Server unit tests are created in a normal VB or C# Test project. The MSTest infrastructure is used to run the tests and view the results. If you have existing test projects containing database unit tests created with Visual Studio 2010 then you must convert these projects before working on them with SSDT. To convert, simply right-click on the test project in Solution Explorer and select Convert to SQL Server Unit Testing project...


SSDT introduces a new SQL Server Unit Test template and type. There are two ways to create SQL Server unit tests in a test project: either by generating tests from a database project opened in SQL Server Object Explorer (SSOX), which creates unit tests with a skeleton T-SQL script for you to complete, or by manually adding unit tests into a test project using the template. If you create tests from SSOX you will have the option to create a new test project or add the tests into an existing project. In many cases you will find the generated skeleton scripts a great starting point so be sure to try these out.

Generating SQL Server Unit Tests from objects in SQL Server Object Explorer

If you have a SQL Server database project in your solution you can generate unit tests from any stored procedure, function or DML trigger defined in that project. To do this, locate the project beneath the new Projects node in SSOX, and then locate and right-click the object you want to test and select Create Unit Tests... (see below). You can generate tests for an individual object, or by selecting its parent node in SSOX, generate tests for all objects in that node. If for any reason you don't have access to a database project for the database that you want to test, you can connect to the database in SSOX, right-click it, and then select Create New Project to create a project for this purpose. 


In the dialog that follows (see below) check all the object(s) for which you want tests generated and choose whether to create a new test project or have the test(s) added to an existing test project. This dialog shows you all the candidate objects in the project that you are working with. If the project has same-database references to other projects or dacpacs, then these references are resolved and the dialog will include candidate objects from the referenced projects and dacpac. Once you have selected all the source objects you want to use, click OK and the project and/or tests will be generated. 


Manually Creating a SQL Server Unit Test

You can also add empty tests to a project manually and define them from scratch. To do this, create or open a test project, right click the project in Solution Explorer, and then use Add > New Item and locate the SQL Server Unit Test template under the SQL Server node. 

In Visual Studio 2010 there is a streamlined Add > New Test option, although note that if you are have either the Premium or Ultimate edition installed, ensure that you use the new SQL Server Unit Test template provided by SSDT and NOT the 'old' Database Unit Test template that was originally installed with Visual Studio 2010 (see below). If you do add a Database Unit Test by mistake then you will need to convert the project as described above before it will build. (To remove temptation you might want to delete or rename the old template in Visual Studio once you have converted all your old-style DB Pro database unit test projects).


Configuring the Test Project 

Whichever route you choose to create database unit tests, when you first add a SQL Server unit test to a test project you will be prompted to configure the project for SQL Server unit testing. In the configuration dialog (see below) choose the database you want to test, and, optionally, a SQL Server database project to deploy before your tests are run. The example below uses the default LocalDB debug database created by SSDT for a database project, but you can use any database, or create a new database from this dialog. The ability to specify or create a database during test configuration allows you to test different instances of the same database without disturbing the source project’s debug database. If you use a LocalDB database you will have to create a new connection from the configuration dialog, and specify (LocalDB)\Projects as the server name. If you choose to deploy a database project when you run your tests, it will deploy using the connection string you specify in this dialog, with the deployment properties specified in the Debug tab of the database project's Properties page. Note that you can change the test project's configuration at any time from the project's context menu or from the SQL menu with the test project selected.


Define your T-SQL Test Script

With test project configuration done, the database unit test designer will be open on the test you created allowing you to define the test script and set test conditions. If you chose to generate a skeleton test script you will now see that in the upper editor pane (below). You now need to either write you test script or tweak the generated skeleton. Your script should exercise the objects in the database that you wish to test. You can either use THROW or RAISERROR during execution to indicate a test has failed, or evaluate the results using test conditions. 


Defining Test Conditions

When your test is executed the test script is submitted to the database and the results are passed back to the test to be evaluated using one or more test conditions. Once your T-SQL script is ready, you should remove the default Inconclusive test condition from the lower pane (which will always fail), and then add one or more appropriate test conditions from the drop-down list and configure them if required in the properties window (see above).  

For more information about writing test scripts and using test conditions, including use of setup and cleanup scripts see Creating and Defining SQL Server Unit Tests in MSDN.

Running the Test

At this point you need to build the test project and run your test and examine the results. Before you build the project for the first time you should delete the default VB or C# test class file created by default in every new test project. You can run the test from the Test > Run menu. When you run your tests from this menu the project will build automatically if you have not built it before or have made changes to the tests.

In Visual Studio 2010 you can view all the tests in your test project and run them selectively from the Test > Windows > Test View window, and view the results in the separate Test Results window (see below).   



In Visual Studio 2012, you can view your tests, run them, and check the results all in one place using the new Test > Windows > Test Explorer window (see below).


Note that you must build the test project after adding a test for the test to be visible in Test View or Test Explorer.

For more information about using database unit testing see, Verifying Database Code by Using SQL Server Unit Tests in MSDN.

Welcome to SQL Server database unit testing in SSDT !