Régis Baccaro

SQL Unit testing with SQL Server Data Tools

October 16, 2014 by

This article on SQL Unit Testing is the second part on the series about SSDT and database development

Part I: Continuous integration with SQL Server Data Tools and Team Foundation Server


In December 2012, a great addition was made to SSDT: The ability to do SQL unit testing.

However, this feature is not available on the free edition of SSDT. For doing unit test with SSDT you will at least need a Visual Studio Professional edition or above.

As you and the team members make changes to the database schema you can verify that these changes are working as expected or if they have broken existing functionality.


Usually you will want to baseline your database and then run some unit tests on the changes that you have made. Personally, I have made a habit out of taking a snapshot of the database I am working with at different stages and at least once a week. This way I can always go back to the state it was before I made the subsequent changes without having to roll back changes with TFS.

Because SQL unit testing of your database is just a “Unit Test Project”, you can create and run database unit test without a database project. However, the only way to auto-generate tests for specific database objects is to use the database project.

When creating a test project from a database project Visual Studio will automatically generate some of classes for you. Most of the plumbing will be done that way. The most important class in that regard is:

Checking for the latest version of SSDT

First you need to check if you have the latest version of SSDT. This is done with the Check for Updates menu under the SQL menu.

SQL unit testing - SQL unit testing - Getting the latest version of SSDT
Figure 1: Getting the latest version of SSDT

When checking for updates you also have the possibility to let Visual Studio do it automatically for you by selecting automatically check for updates to SQL Server Data Tools.

SQL unit testing - Automatically checking for new versions
Figure 2: Automatically checking for new versions

Using Projects in SQL Server Object Explorer (SSOX)

Each time you hit F5, SSDT will deploy your database to your LocalDB. By the way, it is possible to change this behavior by going to the Debug tab of the project’s property page and change the connection string there so your database gets deployed somewhere else than in this local instance.

Creating a SQL Server Unit Test from an existing object in the database

From SSDT You can automatically create stubs for SQL unit testing stored procedures, functions and triggers.

Let us say that we want to test a stored procedure called uspRankCustomer that we created in a previous blog post. See Continuous integration with SQL Server Data Tools and Team Foundation Server for a script for creating the database. Alternatively, use this embedded script with the complete database schema used in this example.

Find the stored procedure you want to create the test stub for and right-click on it in the project node of SSOX (keep in mind that the Projects node will not contain your database project before you have successfully deployed / publish your database).

SQL unit testing - Creating unit tests from Projects node
Figure 3: Creating unit tests from Projects node

In the window that shows next, you can chose if you want to create a VB.Net or a C# test project as well as a list of all the database object that support SQL unit testing and the class name for the test file being created.

SQL unit testing - Choosing which element to create test for
Figure 4: Choosing which element to create test for

I chose C# and after project creation, you are presented with a SQL Server Test Configuration dialog where you can specify which database to run the test on even a secondary connection to validate those tests as well as the option to deploy the database prior to you run your tests.

Remark : If you must test views or stored procedures that have restricted permissions, you would typically specify that connection in this step. You would then specify the secondary connection, with broader permissions, to validate the test. If you have a secondary connection, you should add that user to the database project, and create a login for that user in the pre-deployment script.

In my case I want to run my tests on the database I deployed earlier This is how my setup looks like:

SQL unit testing - Configuring the test project
Figure 5: Configuring the test project

If you look at the solution explorer you will see that following was created:

  • A test project
  • A SqlDatabaseSetupFile
  • A SQLUnitTest class
  • The app.config of the project contains the settings for deployment and database connection

Define test logic

My database is quite simple and contains 3 tables and 3 stored procedures. In my previous post, I created a table and a procedure for ranking customers. I now want to test if my ranking procedure uspRankCustomers works as intended and ranks the customer as specified in the ranking table.

Here is the simple stored procedure used:

Test: For my existing customers I want to update the ranking using the Ranking table.

Step 1: Define the test script

I want to execute my stored procedure and make sure that there are no nulls in the rankingID of the customer table. This way I can be sure that all customers have been ranked!

In Test conditions delete the automatically created condition and create a new one. Change its type to Scalar Value and in the property window make sure the expected value is 0.

SQL unit testing - Writing the unit test
Figure 6: Writing the unit test

Step 2: Running the test

In the Test tab click Windows and then Test Explorer the Test Explorer window should appear, listing your test. Right-click on it and choose Run Selected Test. It should now run and show a green icon if it a success (or a red one in event of a failure).

SQL unit testing - Successful test run
Figure 7: Successful test run

If you remember the previous post in this series, I made it possible to do continuous deployment with MSBuild and Team Foundation Server. I showed how you are able to build and deploy your database changes for each check-in. Well, it is also possible to have this procedure run the test for you. This is useful for running automated tests and analyze the impact of code changes on your tests as part of your build process.

But for now we’ll disable it by removing it in the Build Definition file. On the left pane choose Process and Click the ellipsis on the right side of Automated Test and click Remove in the dialog that shows !!

SQL unit testing - Removing test from build process
Figure 8: Removing test from build process

Running test as a part of your Build Process will be the subject of upcoming blog posts, so stay tuned for more SSDT and SQL unit testing fun!!

Read more about SQL unit testing in Visual Studio: Verifying Database Code by Using SQL Server Unit Tests

Régis Baccaro
Data Tools (SSDT), SQL unit testing, Testing

About Régis Baccaro

Régis is a Principal Consultant at Rehfeld a/s working with databases and administrations since 1999. He has been working with Business Intelligence since SQL Server 2000 and with SharePoint since 2003. He specializes in mentoring, performance tuning and scalable architecture of datawarehouses and bi-solutions. His background and work experience mixes technology and business, providing him a unique edge to contribute to datawarehouse and SharePoint projects, being able to effectively interact with technical and business decision makers. Régis is a frequent speaker at SQL Server conferences and is a board member of the Danish SQL Server User Group and the founder of the SQL Saturday Denmark community event. In april 2014 Régis received the MVP award for SQL Server for his involvment in the product and the community. View all posts by Régis Baccaro