Create a Test Class & First Unit Test with tSQLt

We have set up our tSQLt Database Project in the previous data recipe, Create a SSDT Project Template based on your Database Project. Now it’s time to dive into the wonderful world of tSQLt Unit Testing. In the meantime, I have added my data warehouse to my SSDT Solution and added this project as a Database Reference to my Unit Testing Database Project. If you are unsure how to do this, you can find all the information you need in my previous data cookbook which you can access via the following link: Getting Started With Database Projects & Azure DevOps.

Create a tSQLt Test Class

When working with tSQLt, all unit tests must be created within a specific test class. So as a first step, we will create our first test class.

I have opened my Unit Testing Database Project and we need to navigate to the SQL Server Object Explorer. We search for localdb\ProjectModels, right-click on the Unit Testing database and choose New Query.

To create a test class, we can use a tSQLt Stored Procedure called tSQL.NewTestClass. With the code sample below, I created a new test class called CoreUnitTest.

EXECUTE tSQLt.NewTestClass 'CoreUnitTest'

After creating the test class, we can take a look at what this procedure has done for us. So let’s import the test class by using SqlCompare. If you don’t know how to use SqlCompare in SSDT, feel free to take a look at my previous data recipe where we used SqlCompare to do the initial setup of our Unit Testing Database Project.

If we open the created object (CoreUnitTest), we can see that the executed Stored Procedure created a schema with the mentioned name. And, added an extended property which identifies it as a test class.

Creating a Unit Test

We have created our test class so the time has come to create our first unit test. For this unit test, we are going to check if the AdventureWorksDW database has the expected collation.

To create our first unit test, we are going to add a new Stored Procedure called Test_DatabaseCollation in our Unit Testing Database Project. I created a new folder where I will add all my Core Unit Tests. In the Stored Procedure, we are going to use the following code:

CREATE PROCEDURE CoreUnitTest.Test_DatabaseCollation
AS
	-- Assemble
	DECLARE @actual VARCHAR(50);
	DECLARE @expected VARCHAR(50);

	-- Act
	SET @expected = (SELECT CONVERT(VARCHAR(50), SERVERPROPERTY('Collation')));
	SET @actual = (SELECT CONVERT(VARCHAR(50), DATABASEPROPERTYEX('[$(AdventureWorksDW)]', 'Collation')))

	-- Assert
	EXEC tSQLt.AssertEquals @expected, @actual;

GO

Explaining the code for the Unit Test

If we take a look at the code that we used above, we can identify 3 sections: Assemble (or Prepare), Act, and Assert. These sections typically are part of every unit test.

First, we need to prepare everything for the unit test. In this example, we don’t have anything to prepare but this would be the place where to prepare your dummy data for the unit test.

To continue, we need to define the Act phase, this is the place where you want to apply the logic that needs to be executed to get the final result. In this case, retrieving the server collation and the database collation.

Finally, a Unit Test ends with the assert phase. In this case, we are validating if the database collation is the same as the server collation.

Important to notice as well is that the Stored Procedure (Unit Test) is created in the CoreUnitTest schema. By doing this, tSQLt can identify that this Stored Procedure is a Unit Test.

Executing the Unit Test

We can use another default Stored Procedure from the tSQLt Framework to execute our Unit Test. The T-SQL code that we are using:

EXEC tSQLt.RunAll

If we execute this code on the Unit Testing Database, we get the following result:

If a Unit Test fails, you will get a similar result as shown in the screenshot below:

Enjoy!
Bon Appétit!