This SQL unit testing article is based on the fact that we often learn from the mistakes so the idea is to highlight the mistakes to understand them clearly so that they can be avoided straight away.

In this article the ten most common SQL Server unit testing mistakes and their remedies are discussed.

Let us get familiar with the mistakes in SQL unit testing keeping in mind we are not learning the mistakes rather we are learning from the mistakes to improve SQL unit testing skills.

SQL Unit Testing Mistakes List

It is better to list the SQL Server unit testing mistakes first to get an idea of what we are after and then briefly shed some light on the mistakes and their remedies one by one.

Here is the list of the ten most common unit testing mistakes:

1. Writing SQL unit tests without understanding
2. No standard naming conventions
3. Writing SQL unit tests to run once
4. Relying solely on SQL unit test results
5. Depending on actual data
6. No testing pattern in place
7. Repeating setup code in all unit tests
8. SQL unit testing with constraints applied
9. Isolating SQL unit tests from database lifecycle management (DLM)
10. No clean up strategy in place

1. Writing SQL unit tests without understanding

If you straight away start writing SQL unit test without knowing the concepts of database unit testing then you are making a big mistake.

A couple of unit tests may survive but ultimately you are going to find yourself in the middle of nowhere because knowledge based understanding of database unit testing is a must in order to write, run and maintain SQL unit tests professionally.

The minimum requirement is to understand the basic principle of unit testing and SQL Server unit testing framework which is going to be used to implement your unit tests.

Basic principle of SQL unit testing

The basic thing when it comes to unit testing is to understand AAA principle which defines how a database unit test should be written.

The AAA has the following purpose:

1. Arrange

   This is where you arrange your SQL Server unit testing code such as defining expected results

2. Assemble

   This is where you assemble your code by calling the database object under test and taking down its results.

3. Assert

   This is the final check which compare your expected results (based on Arrange) with the actual results (based on Assemble) and if all goes well the test has passed else the test fails.

To get better understanding of AAA principle please refer to article tSQLt – A Forgotten Treasure in Database Unit Testing

Knowing your unit testing framework

If you know the basic principle of SQL unit testing but does know how to implement it in your unit testing framework then this is again a mistake.

Getting familiar with your SQL Server unit testing framework is also a must plus you should be able to defend your SQL unit testing framework when challenged.

It is important to understand whether the unit testing framework you or your development team has chosen is capable of meeting the requirements or not but apart from this you also need to understand how implement standard unit testing concepts in your chosen unit testing framework.

I would strongly suggest tSQLt, one of the most suitable SQL Server unit testing frameworks written in T-SQL and fully support AAA principle implementation along with advanced database unit testing tasks.

Please take a look at tSQLt – A Forgotten Treasure in Database Unit Testing to know more about tSQLt.

2. No standard naming conventions

If you are not following standard naming convention in your SQL unit tests then sooner or later you may find yourself in a dark room of ambiguity and may end up spending your time in clarifying things than writing unit tests.

It is crucial to have some standard naming conventions in place which must be unanimously chosen by your development team.

There are two things you must take into account regarding naming conventions:

1. Unit testing framework supporting naming conventions
2. Naming conventions about naming tests

Unit testing framework supporting naming conventions

You must wisely choose such a SQL unit testing framework in which naming conventions can be easily followed.

Once you are confident that your chosen SQL Server unit testing framework supports naming conventions then those naming conventions must also be shared with your team and there should be a unanimous decision to what to use.

Here again, my vote goes to tSQLt which makes it easy to use and standardise naming conventions.

TSQLt groups the related unit tests into a single test class which if named sensibly is easy to find, modify and maintain afterwards.

Naming conventions about naming tests

According to Dave Green if you name your test class after the name of your database object under test then this makes unit tests easy to understand and maintain.

For example if your object under test is AddBook stored procedure then it is good to create a test class AddBookTests under which all the unit tests related to this object can be grouped and easily located.

This scenario can also be mapped to requirement focused objective and in that case think of DailyBookReport object created to meet business specification and then the test class should be named as DailyBookReportTests.

Please remember that these examples are more likely to be applicable to tSQLt unit testing framework because of the generic naming convention support by it.

Please check Conventional SQL Unit Testing with tSQLt in Simple Words to see some more naming convention examples.

3. Writing SQL unit tests to run once

One of the biggest SQL unit testing mistakes is to write unit tests to run once which means apparently there is nothing wrong with the unit test as long as it has not been run twice or more.

This in itself is a problem because SQL unit tests should not be restricted in terms of how many times they can run as this can create chaos in unit testing.

You should design your SQL unit test such that it should be consistent even it is run one hundred times.

In other we should look for the following two things:

1. SQL unit tests must be repeatable
2. SQL unit testing framework must support repeatable unit tests

You should only write repeatable unit tests and this heavily depends on your choice of SQL Server unit testing framework such as tSQLt which supports repeatable unit tests by design.

4. Relying solely on SQL unit test results

A passed unit test does not always mean it is serving the purpose so you should not totally rely on the end result of the SQL unit test.

Think of a unit test intended to meet some requirement by mistake shows green signal without even knowing what is being checked in it.

As a database developer you have to be vigilant all the time when creating and running SQL unit tests because it is possible that a test has passed just because it does not do anything so it does not fail.

In other words please bear in mind if a SQL unit test has passed then one of the two things have happened:

1. Unit test meets the business specification
2. Unit test has been coded to just pass

This mind set helps you to investigate issues related to SQL unit tests much earlier.

Most of the third party SQL unit testing tools now default there new unit test to fail which is a safer approach, however, there is still a possibility to commit this mistake at later stage of writing unit tests.

The database unit tests written in tSQLt give a quick insight to be able to detect whether they are actually meeting a business specification or merely running as place holders.

5. Depending on actual data

Another SQL unit testing mistake is when your unit test depends on actual data from the database. For example, sometimes in order to make SQL unit tests more and more realistic one might think of using the actual data in the database to be used for SQL unit testing.

Please remember according to the database unit testing experts including Dave Green when it comes to unit testing data itself is a dependency.

For example if you write a SQL unit test against your dev database which must be restricted to specific ten records for this test then to create and run this test is a big mistake because your unit test is depending on a specific set of data in the database.

I have seen in the past this happens mostly when you are testing report output procedures to verify the data which is not a good SQL unit testing practice in two ways:

1. Your unit test is not your data verification test
2. If your unit test depends on the data in the database then it is very limited

In other words you should separate your SQL unit tests from data verification tests (their details are beyond the scope of this article) and your unit tests should not rely on actual data in the database rather they should have their own test data closer to the realistic data which is enough for a unit test to comply with standard practices.

Here again tSQLt unit testing framework is a good choice.

Please refer to Conventional SQL Unit Testing with tSQLt in Simple Words to see how test data is embedded in unit test which does not need to rely on actual data in the database then.

6. No testing pattern in place

If you are not following any testing pattern then you are committing a mistake because sooner or later this is going to be confusing because of the way testing patterns work.

The two most common testing patterns or strategies are as follows:

1. Conventional SQL unit testing
2. Test-driven database development (TDDD)

At least one of the above testing patterns must be followed and tSQLt supports both despite the fact that it is by design test-driven database development ready.

You can read more about these testing patterns in the following articles:

1. Conventional SQL Unit Testing with tSQLt in Simple Words
2. Fundamentals of Test-Driven Database Development (TDDD) with tSQLt unit testing

7. Repeating setup code in all unit tests

Those who heavily write SQL unit tests must know the importance of setup code which is often same but repeated multiple times.

The setup code is getting your testing ingredients ready to be used which can be defined once and referenced as many times as possible rather than repeating in every single unit test.

The advanced unit testing frameworks like tSQLt completely supports setup procedure to be defined once and reused as much as possible.

Repeating setup code in all the unit tests is not a mistake but it may prone to mistake (error) so please avoid this and try to define your setup code in a setup procedure supported by unit testing frameworks like tSQLt.

8. SQL unit testing with constraints applied

In a typical scenario when you are writing SQL unit test for a stored procedure which manipulates a table then you don’t care much about whether the constraints on the table should be removed or not for the unit test.

If your SQL unit test interacts with a table with constraints such as primary key intact then you are diverting from database unit testing standards because of the following facts:

1. A table in unit testing should not include its constraints
2. Constraints on the table should be unit tested separately one by one

TSQLt framework provides stubs to mock actual tables with no data and constrains so that they can be readily unit tested.

9. Isolating SQL unit tests from database lifecycle management (DLM)

If your Database Lifecycle Management (DLM) strategy does not include your SQL unit tests then this is again a mistake since the purpose of creating SQL unit tests is to become part of Database Lifecycle Management (DLM).

This means the SQL unit tests ideally should run in the following two stages:

1. Manually when deploying changes to debug database
2. Automated when deploying changes to shared dev database and other stages

TSQLt offers both manual and automated flavours provided the database developer has experience of working with Database Lifecycle Management (DLM).

10. No clean up strategy in place

If there is no clean up strategy in place then you are missing a major thing in your SQL unit testing strategy.

In the absence of a standard clean up procedure your SQL unit tests might end up in the Production database which is not at all desired and must not be allowed.

You must enforce a strategy to drop the tests including the testing framework from the database before the database is moved to the final stage which is production which also demands a very wisely chosen source control strategy.

TSQLt has built-in framework and tests clean up support.

By avoiding the mistakes and adopting their remedies discussed in this article you are not only improving your SQL unit testing skills but also helping your development team setting up better standards in SQL unit testing for your organisation.

Table of contents

• Author
• Recent Posts

Haroon Ashraf

Haroon’s deep interest in logic and reasoning at an early age of his academic career paved his path to become a data professional.

He holds BSc and MSc Degrees in Computer Science and also received the OPF merit award.

He began his professional life as a computer programmer more than a decade ago, working on his first data venture to migrate and rewrite a public sector database driven examination system from IBM AS400 (DB2) to SQL Server 2000 using VB 6.0 and Classic ASP along with developing reports and archiving many years of data.

His work and interest revolves around Database-Centric Architectures and his expertise include database and reports design, development, testing, implementation and migration along with Database Life Cycle Management (DLM).

He has also received passing grade to earn DevOps for Databases verified certificate, an area in which he finds particular interest and potential.

View all posts by Haroon Ashraf

Latest posts by Haroon Ashraf (see all)

• SQL Machine Learning in simple words - May 15, 2023
• MySQL Cluster in simple words - February 23, 2023
• Common use cases of SQL SELECT Distinct - February 2, 2023