Walk through: Create new SQL Server Data Project and Unit Test - RubixRevenge/SSDT_UnitTestingFunctions GitHub Wiki

Create Multiple Unit Tests against the same SQL Server Function

Introduction

This wiki is designed to walk you through the entire process of having an empty database project, importing an existing development database (having only one function), and creating several unit tests to verify output. This is a minimal project and focuses on some lessons I've learned over the last few weeks. First, how to create unit tests for functions in a SQL Server Database Project (using Visual Studio), and second, strategies for refactoring some of your query code into functions so that it is unit testable and more importantly does not slow down your queries to an unacceptable degree.

My need to pursue this path was having complex queries in SQL Agent jobs that were generated by manual tests. I read somewhere that the approach is called "cowboy coding," where you get bugs reported to you, verify it for yourself, and make tweaks in the production database. But now I know better, I am using this Github repository and wiki as a means to share my purpose, methods, and materials. This is based on my best and current understanding of the technology. If you believe my reasoning or content is flawed, please tell me how so that I too can learn from it and share.

Assumptions

I assume a basic familiarity with T-SQL and SSMS, such as creating functions using the SSMS GUI or by T-SQL code. The walk-through will not go to such as low level that it completely for beginners.

Materials

  • SQL Server Management Studio 2017
  • Local SQL Server Express Database (compatibility 2017)
  • Visual Studio 2017 Professional (you should be able to use the Community edition, but I can't verify that all features or layouts are there as presented in this wiki).

Phase 1: Use SSMS (SQL Server Management Studio) to create a database and function

  1. Using a local database or other development database server, create a new database "SSDT_UnitTestingFunctions"

Images/SSMS-01-Create database in local db.png

  1. Create a new function "ConvertInchesToCentimeters". I created mine using the SSMS GUI.

Images/SSMS-02-Create simple function.png

Now that's done, you can close SSMS and open Visual Studio.

Phase 2: Use Visual Studio to create a SQL Server Data Project and Unit Test

  1. Create a new SQL Server Database Project "SSDT_UnitTestingFunctions".
  2. Right-click the empty database project and select import > database

Images/VS-00-ImportDatabase.png

  1. When prompted, connect to the database on your local database. I have redacted sensitive information.

Images/VS-01-ConnectToLocalDatabase.png

  1. Accept the defaults and click "start".

Images/VS-02-AcceptDefaults.png

  1. You should see the contents of your database appear in the dbo folder.

Images/VS-03-DatabaseContentIsImported.png

  1. Right-click the function and select Create Unit Tests...

Images/VS-04-CreateUnitTests.png

  1. As this is your first unit test, allow the popup to create a new test class. As a style preference, I prefer to have one unit test class per function being tested. I named the project "UnitTestFunctions" and the new class "ConvertInchesToCentimeters.cs".

Images/VS-05-Define names for unit test project and unit test.png

  1. You will be prompted to configure your unit test project. Connect to the local database you imported from in step 4. For deployment, select the name of your database project (in this case it should be the only option). Select "debug" as the deployment configuration.

Images/VS-06-Configure test data connections and deployment options.png

  1. "UnitTest1.cs" is created by default, but as it has no use in our scenario, delete it.

Images/VS-07-Delete UnitTest.png

  1. At this point you will see the test designer appear with your first unit test. There are some setup actions you will take every time you create a new unit test.

  2. Delete the "inconclusive test" that appears by default. Images/VS-08-Default view after configuration of unit test.png

  3. Rename the test to describe what you are testing for. In this case I wrote "If_Given_0_Inches_Return_0_Centimeters". Again, this is a style preference to maximize legibility for someone else looking at my tests. Images/VS-09-Rename test to be descriptive.png

  4. Add a new scalar test condition. Images/VS-10-Add new scalar test condition.png

  5. This is a style preference. Write down what you are thinking in code comments. While the test name is meant to be descriptive and accurate, what about someone else looking at your code? What if you are looking at your own code two years later? Images/VS-11-Describe test in comment.png

  6. Enter the expected value in the properties panel. Images/VS-12-Enter expected scalar value.png

  7. Run the test. In this case, the test is expected to pass.

Images/VS-13-Run the test in test explorer.png

  1. There are two different ways to create a new unit test. This is my preferred way. Repeat step 8: Right-click the function and select Create Unit Tests...

Images/VS-14-Create new unit test.png

  1. Here's the key: rather than creating a new class, select "insert new unit test" into the unit test class that we created earlier "ConvertInchesToCentimeters.cs".

Images/VS-14-Insert unit test.png

  1. Now you have your default code.
  • Rename the test to "If_Given_1_Inch_Return_2_54_Centimeters".
  • Set the @Inches parameter value to "1".
  • Remove the default "Inconclusive" test condition, and add a "Scalar" test with an expected value of 2.54.

Images/VS-15-Update test names values and tests.png

  1. Run the tests. They pass.

Images/VS-16-Run tests.png

  1. Create more unit tests and run.

Images/VS-17-Create more unit tests and run.png

And that is how test the same function using different unit tests.