Unit testing: What it is and why it is important for T-SQL code!

This article is the first of a series of posts in which we will discuss the importance of unit testing applied to database development using T-SQL language for SQL Server and Azure SQL Database. Let’s start!

What is the Unit Test?

Unit testing is a software testing level that aims to test a discrete piece of code. The word “unit” refers to the smallest piece of code that can be tested separately, it could be a function or a procedure that can be tested in isolation. In a database solution, the “unit” is typically a stored procedure, a trigger or a user-defined function. When we perform the unit test of a stored procedure or a function, the test should be focused on the code we are testing and not how the procedure, function and so on interacts with other procedures or functions. It is very important to define the System Under Test (SUT) first and isolate it! System Under Test must not be influenced by other procedures or functions called within the one you want to test.

Each test must be easily repeatable. As the code is written, during the development, it must verify the previous requirements as well. The repeatability of the test will give us the ability to automate it, that is an essential condition for integrating automatic tests within a Continuous Integration process.

Each test must verify one condition at a time. Tests should reflect software requirements. If we do so we will get a series of discrete tests by each unit. Verifying one condition at a time, it will be possible to identify which conditions have not been verified and which ones meet the requirements, confirming whether or not all the individual parts work.

Unit Tests are a development tool, they are typically implemented by the person who developed the code, anyway the Software Testing Team can implement additional unit tests to increase tests coverage.

What benefits will I get by writing unit tests?

Unit tests convey safety! How many times, after a change request, have you heard these phrases:

Why do we need to change that stored procedure?

What do we do if it no longer works? It is so complex!

If I do that, I will have to try every single requirement!

Do you know how long it will take? It is impossible to know!

Often, even the author of a complex piece of code wants to avoid changes because he/she fears consequences. He/She will have to test all cases with the fear of forgetting something, with uncertainty about the amount of time for development. On the contrary, being able to count on a series of repeatable tests, the developer will not be afraid of modifying complex parts of T-SQL code because he/she knows that he/she will be able to count on the unit tests to verify expected results against certain input data.

Unit tests provide documentation of the software requirements because a direct link is created between each test and the requirement it verifies.

Unit tests are preparatory to the design phase (TDD methodology): they force you to think how to organize properly. It’s useful and worthwhile to think about tests from the very beginning, which improves the design phase: what needs to be done, which specifications are required. As a result, nobody will rush into writing code. Thinking about tests from the very beginning we will improve the design phase, we will think in advance about what needs to be done, most of the design will be done in advance based on the required specifications.

Unit tests simplify the error checking process because they identify, accurately, which test case failed.

If you have understood the importance of writing unit tests for your T-SQL code, you are wondering: Where can I start? In the next section we will introduce tSQLt, one of the most popular frameworks for writing unit tests in SQL Server.

Introduction to tSQLt

tSQLt was developed by Sebastian Meine and Dennis Lloyd, it is the open source framework for implementing unit tests in T-SQL for SQL Server. tSQLt is written in T-SQL, it works with all editions of SQL Server starting from SQL Server 2005 SP2, it requires SQL CLR to be enabled.

tSQLt provides the following features to simplify the creation and management of unit tests:

• Tests are performed automatically within transactions; this approach was adopted to keep the tests independent and reduce the work of cleaning any temporary structures used during the tests itself
• Tests are grouped within a schema, this allows you to better organize your tests and use common configuration methods
• The output can be in plain text or XML. XML format simplifies the integration with Continuous Integration solutions
• The framework offers the possibility to create fake tables and fake views as well as “spies” stored procedures. It offers these tools to test the code in isolation

You can download the latest version of tSQLt from the download page of the tSQL site (an update was released a few days ago), the complete guide is available here. A quick guide is available for those of you that are in a hurry to be “hands-on” :)

That’s it for now, in the next article we’ll cover the anatomy of a unit test for T-SQL language!

Enjoy!