Getting Started with SQL Server Database Unit Testing in SSDT

Getting Started with SQL Server Database Unit Testing in SSDT

Rate This
  • Comments 11

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 ! 

Leave a Comment
  • Please add 5 and 7 and type the answer here:
  • Post
  • Wow great idea, will try this out asap, and let you know how it goes.

    Would you recommend the Premium edition of VS 2012 or would Pro do fine?

  • Denis, VS 2012 Pro is all you need in order to use SQL Server unit testing in SSDT.  Obviously the Premium and Ultimate editions have many great features, but they are not specifically leveraged or required by SSDT.

  • I am not finding the SQL Server Unit Test template. I have VS 2010, SSDT 2010, VS 2012, and SSDT 2012 and have not found the template anywhere. Any thoughts?

    Thanks for the article!

  • Hi Mikah, assuming that you are following the instructions above and using a VS Pro or higher edition, then the template should be visible.  If you have Premium or Ultimate, then in VS 2010 SQL Server Unit Test template should be visible in the Add New Test dialog alongside the existing Database Unit Test - if this applies to you, do you see the old template?

    Try running VS from the command line using the following, which will reset the default templates.

    VS 2012 -> C:\>"C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\devenv.exe" /ResetSettings

    VS 2010 -> C:\>"C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe" /ResetSettings

    If this doesn't work then please describe the problem on the forum (link below), including a full description of the environment (edition and versions of VS and SSDT) and the steps you're using and what you see, and we'll try and resolve it with you.

    Forum: social.msdn.microsoft.com/.../threads

  • Has anyone managed to get automatic deployment of projects working with the SSDT Unit tests when using composite projects?

  • This all works fine and when asked to to create a new test project when you create yuour first unit test create a the test project at the solution level. Which again all works well.

    However if I try and move the test project into a more appropriate solution folder (i.e. 'UnitTests'). I then get an error when I try to add anew test, it states:

    System.ArgumentException

    Project with name Solution1.Server.UnitTest doesn't exist in solution

      at Microsoft.VisualStudio.Data.Tools.Package.SharedUtilities.VsUtilities.GetProjectFromSolution(String projectName)

      at Microsoft.VisualStudio.Data.Tools.Package.UnitTesting.UI.SqlUnitTestWizard.TryEnsureCorrectProjectImportsAndReferences(String projectName, String fileBeingOpened)

      at Microsoft.VisualStudio.Data.Tools.Package.UI.DataPackage.OnIdle()

      at Microsoft.VisualStudio.Data.Tools.Package.Project.VsPkgIdleProcessingComponent.FDoIdle(UInt32 grfidlef)

    I have tried making the namesapces the same, as well as adding references but nonthing seems to fix this?

  • Is the Test > Windows > Test Explorer window (see below) available in VS Professional with SSDT 2012?  I am able to get as far as defining test conditions but can't get to your next step.  I am not sure that VS Professional has Test View or Test Explorer.  Thanks

  • When is the custom static rules and custom refactoring support going to arrive in SSDT/VS2012.

    I've made quite an investment of time and effort with these features in VS2010.  I'd hate to think I can't carry it forwards into SQL Server 2012.

  • Hi, I was trying to follow the instructions in this article (which I think are very similar to the ones I saw here msdn.microsoft.com/.../jj851212(v=vs.103).aspx) and found myself stuck at the step where you right click the object and see the option "Create Unit Tests". I am not able to see this option and I am using Visual Studio 2012 Professional Edition. Any suggestions on why is this option not being displayed?

    Here I show an image of the options I see (it's in spanish but it's understandeable) http://i.imgur.com/WkQ6PCB.png

  • In my project 'create unit tests' disabled if stored procedure query linked server.

    Is this expected?

    Microsoft Visual Studio 2010 Version 10.0.40219.1 SP1Rel

    SQL Server Data Tools   10.3.30914.0

    Microsoft SQL Server Data Tools

    SQL 2012

  • Hi, I really liked the idea and was able to create some basic unit tests, however for most of my stored procedures the Create unit test option is disabled. Is there any prerequisites in creating test cases, I am using CTE's in those Sprocs and create unit test option is disabled. Thanks

Page 1 of 1 (11 items)