Esat Erkec
Reviewing the a SQL unit test details with detailed explanation

SQL Unit Testing reference guide for beginners

August 11, 2023 by

In this article, we are going to learn the basics of SQL unit testing and how to write a SQL unit test through the tSQLt framework.


Unit testing is a testing technique that allows us to the test functionality of the smallest and programmable part of the software. Our goal to write unit tests is to make sure that every atomic part of the software works as expected. In terms of database development, unit testing helps us to test the functionality of the database’s small units of objects. At this point, this question may appear in your mind:

How can we implement unit tests in SQL Server?

tSQLt is a powerful, open-source framework for SQL Server and we can use this framework to write unit tests for SQL Server.

Installing tSQLt framework

To download the tSQLt framework, we can go to the website and then download the latest version of the tSQLt framework. In the download file, we can find PrepareServer.sql. Initially, we will execute PrepareServer.sql to enable CLR and installs a server certificate that allows the installation of the tSQLt CLR.

Installing the tSQLt framework

As a second step, we need to execute the tSQLt.class.sql file to install the tSQLt functionalities. After execution of this script, this message will appear thus we can understand tSQLt framework has been installed successfully.

The output message after the installation of the tSQLt framework

Writing a basic SQL unit test

After learning the benefits and advantages of SQL unit testing, we can start to write our first unit test. First of all, we need to create a test class so that we can gather and organize our tests under this class. In addition, a test class allows us to execute tests as a group that belongs to the test class. A test class can be executed as follows:

The tSQLt framework includes a bunch of test methods for the SQL unit testing operations and we can use these methods for different test scenarios.

  • tSQLt.AsserEquals
  • tSQLt.AssertEqualsTable
  • tSQLt.AssertEmptyTable
  • tSQLt.AssertEqualsString
  • tSQLt.AssertEqualsTableSchema
  • tSQLt.AssertLike
  • tSQLt.AssertNotEquals
  • AssertObjectDoesNotExist
  • AssertObjectExists
  • AssertResultSetsHaveSameMetaData
  • Fail

Let’s start with the very basic function of the tSQLt framework and thus try to figure out how the tSQLt framework works.

AssertEquals: This method compares the actual output value with the expected value. This method takes three different parameters:

@expected: The expected parameter value specifies the value which we expect after the execution of the test process.

@actual: The actual parameter specifies the value returning from processing during the test.

@message: This parameter is optional and helps to customize the message when the actual and expected values are not equal.

The following scalar-valued function calculates and returns the age of the passed parameter.

Now, we will write a unit test for this function.

If we interpret this function line by line ;

At first, we give a name to our test function. At this point, we need to give more readable name to our test procedures

In this part of the code, we declare a variable that passed as input to the scalar-valued function as an input.

We declare the expected value for our SQL unit testing and then assigned it to an expected value which we wait for after the execution of the function.

In this code part, we declare a variable for actual value and assigned the function result to this variable.

Lastly, we compare the expected value to the output of the age-calculating function. Now, we are going to execute our test case and are going to look at the result of the test case.

Result of the tSQLt unit test

The result of the test execution shows us, the age-calculating function passed the test. However, if we change the excepted value of the test case, the output of the test result will like as the following.

SQL unit testing result if the unit test does not the pass the test

Arrange, Act and Assert Patterns in SQL Unit Testing

In unit testing, there is a pattern that is widely used to structure the unit test. This pattern is known as the AAA pattern and all A defines the Arrange, Act, and Assert.

Arrange: In this part of the unit test, the test inputs, and required objects are defined, and also the expected result which we wait for after the execution of the test is defined in this part of the unit test.

Act: This is where we invoke the code we are testing.

Assert: It is the part where the result from the tested method and the result we expect is compared.

Using the AAA pattern is the most effective way to write an effective unit test and we can use this pattern in the SQL unit testing. In the following, we can see how to implement this pattern in our previous unit test.

SQL Unit testing Arrange,Act,Assert pattern

Isolating the dependencies in SQL unit testing

Isolating SQL unit testing from the dependencies will help to develop more robust and less fragile unit tests because the main purpose of SQL unit testing is to test the functionality of the smallest part of the database objects. At this point, we need to fake (mock) the dependent objects which we want to test. Mocking is the creation of fake versions of the objects that the code depends on while testing the code. The reason this is done is to do the testing at the true unit level when doing unit testing. In the tSQLt framework, we can use the following functionalities to isolate the dependencies:

  • ApplyConstraint
  • ApplyTrigger
  • FakeFunction
  • FakeTable
  • RemoveObject
  • RemoveObjectIfExists
  • SpyProcedure

Writing an advanced SQL unit test

When we look at the following procedure, it returns the total sales amount of the customers according to their ages. However, this procedure has two dependencies, the first one is related to the foreign key constraint and the second on
e is including a scalar-valued function.

Under this circumstance, we need to avoid these two dependencies to write a more robust unit test. The FakeTable function of the tSQLt framework helps us to create an empty version of the specified table without constraints. So that, we can insert any value into it during the execution of the test. The other method we will use will be FakeFunction because the age-calculation function gets a date parameter as input and affects the result of the procedure according to the customer’s age. First, we are creating a new test class.

In this step, we need to create a mock of the function that calculates the age because our result set is changing according to the result of this function. So, we can ensure that the function will always return a constant value.

After all these explanations our unit test design will look as below.

The result of the unit test will not pass the test.

Result of the SQL unit test for an advanced unit test

Now we will tackle the SQL unit testing code details line by line according to the AAA pattern and make a comprehensive explanation.

Reviewing the a SQL unit test details with detailed explanation

Arrange: In this part of the unit test, we faked the age calculation function, so that we enable it to return a hard-coded value and we designed the SQL unit test according to the certain value. Besides this, we created the faked versions of the customer and sales_customer tables because this table should be empty during the unit test and the procedure worked on the defined rows in the unit test. We also created the actual and expected tables to populate the expected row and actual rows to make a comparison at the end of the unit test.

Act: In here, we executed the procedure which we want to test and populate the returning result set into the actual table.

Assert: In this last part of the unit test, we compare the actual and expected results

When we look at the bit

  • The “<” sign shows the row exists in the expected table but does not exist in the actual table
  • The “>” sign shows the row exists in the actual table but does not exist in the expected table
  • The “=” sign shows the row already exists in the actual and expected tables


In this article, we learned basics of the SQL unit testing and how to write a SQL unit test using the tSQLt framework. Then, we highlighted the fundamental benefits of writing unit tests which are below:

  • Improves the quality of the code
  • Helps to find out the bugs before the production deployments
  • Accelerate the deployment velocity
Esat Erkec