Haroon Ashraf

Why you should cleverly name Database Objects for SQL Unit Testing

May 29, 2019 by

This article is focussed on clever database object naming from both development and SQL unit testing point of view.

This article also highlights the importance of naming database objects going through different development transitions including the SQL unit testing phase due to the agile nature of requirements.

The purpose is to understand the long term positive effect of clever naming of your database object right from the beginning and most importantly during the SQL unit testing when you are up against not so fixed business requirements.

About Naming database objects

Let us first talk about naming database objects in general.

Typical development scenario

A typical database development scenario (also known as conventional database development style) is as follows:

  1. You create a new database (if it does not already exists)
  2. You create a database object (based on internal or external business requirements)
  3. You write code for the database object (to meet internal or external business requirements)
  4. You create a SQL unit test for the database object (to ensure the object is working properly)
  5. You run the SQL unit test for the database object (to validate business requirements embedded in the database object)

Special development scenario

A special database development scenario (such as test-driven database development) has the following steps:

  1. You create a new database (if it does not already exists)
  2. You create a SQL unit test for a potential database object (to meet internal or external requirements)
  3. You run a SQL unit test for the potential database object (which must fail to comply with test-driven database development)
  4. You create the potential database object (to work properly to pass the unit test)
  5. You run the SQL unit test (to validate the internal or external requirements)

What about naming a database object

If we look at both typical and special database development scenarios it seems it does not even matter how we name the object.

The truth is, it matters a lot as you proceed further and this is what I am going to explain in this article in the context of SQL unit testing.

The time you think of the potential object (in test-driven database development) or the actual object (in conventional database development) to be created based on business or internal requirements you must think of naming the object cleverly because the database object is going to be referenced throughout its lifetime by that name unless refactoring requirement to rename the object arrives.

Introducing SQL Unit Testing Object

Please remember that I am coining a new term SQL Unit Testing Object to be interchangeably used with database object where by SQL unit testing object I particularly mean the database object which must be unit tested to validate it is functioning properly.

How you should name the object

Please consider the following things when naming your database or SQL unit testing object:

  1. The name of the object should not be based on the type of the object
  2. The name of the object should not be confusing
  3. The name of the object should be standardized
  4. The name of the object should reflect its purpose
  5. The name of the object should not be verbose (too long)
  6. The object name should be based on considering SQL unit testing in mind

The sixth point in the above list is the most important point.

Let us understand database object naming in the form of two scenarios where each scenario represents a business requirement that keeps on changing with time.

Naming SQL unit testing object to adopt to the requirements changing over the time.

Clever naming of SQL Unit Testing Objects

Pre-requisites

There are some pre-requisites before we proceed further to understanding database object naming from development and SQL unit testing perspective.

T-SQL and TSQLt familiarity

This article assumes that the readers are well familiar with T-SQL scripting and tSQLt which is a very well-known database unit testing framework for SQL Server.

Setup sample database (Toyshop)

This article also assumes that a sample database named Toyshop has been created with the following tables:

  1. Toy
  2. ToySale

Please use the following script to setup the sample database Toyshop:

Creating sample database named Toyshop.

Setup tSQLt unit testing framework

You need to install tSQLt unit testing framework in order to follow the examples in this article. You can check

tSQLt.org to download tSQLt unit testing framework.

Please refer to Conventional SQL Unit Testing with tSQLt in Simple Words article for a better understanding on how to install tSQLt framework if you have not installed it before.

Open tSQLt.class.sql file in SSMS (SQL Server Management Studio) and Run tSQL.class.sql script against the sample database Toyshop.

TSQLt has been installed successfully.

Conventional SQL unit testing

We are using conventional SQL unit testing in this article in order to focus entirely on the objectives (naming SQL unit testing object rather than choosing the best unit testing methodology) although I strongly recommend test-driven database development (TDDD).

Scenario 1: First business requirement

The first case is when you receive the business requirement for the first time.

Business requirement

“The end user should be able to see a sales report for all the toys with their names which have been sold.”

Potential SQL unit testing or database object

As per conventional SQL unit testing or test-driven database development in order to meet the business requirement, you have to come up with a potential SQL unit testing object (database object) capable of meeting the business specification.

Naming SQL unit testing object

This is the time when you have to cleverly choose the name of the database or SQL unit testing object.

Hold on! Before choosing the name of SQL unit testing object do you really need to decide the type of object?

For example, is the potential object going to be SQL function or SQL stored procedure?

The key is to forget the type first and simply name your SQL unit testing object to denote its purpose only which must not give clue to its type.

We are naming the object as ToySalesReport due to the following reasons:

  1. The object name clearly speaks out its purpose and the purpose must map to an internal or external business or system requirement
  2. The object name is not bound to a specific type such as stored procedure or function which is beneficial in the long run
  3. The object name is easy to understand and does not require additional documentation to explain which business requirement it is intended to meet

Type of SQL unit testing object

Let us follow the principle of least privilege here in the context of choosing the type of SQL unit testing object. This means to think and ask do you really need a stored procedure here or not? The answer is No.

A stored procedure is not required because this requirement can be easily fulfilled by a simple SQL view.

Create ToySalesReport (SQL View)

The object has been created so we are going to create a SQL unit test next.

Create ToySalesReportTests class

In order to write tSQLt unit tests, we have to create a test class first.

Please create ToySalesReportTests schema (test class) as follows:

Create SQL unit test to check object functionality

Once the test class is created, the next step in SQL unit testing from tSQLt framework perspective is to create a unit test to check if the object is functioning properly (in conventional SQL unit testing).

Create SQL unit test to check object is functioning properly (meeting business requirement) as follows:

Run SQL unit test

Run the tSQLt unit test to see the results:

TSQLt unit test has passed.

The SQL unit test has passed.

Think of this SQL unit testing example from clever naming of potential object point of view as well.

Things are going to be clearer as we move on to the next scenario.

Scenario 2: Change in business requirement

As you know that SQL unit test written in tSQLt in the previous scenario has passed which ensures that ToySalesReport object meets the business requirement.

Let’s say after some time there is a change in business requirement and we are asked to meet the updated business requirement.

Business requirement change

“The end user should be able to see a sales report for all the toys with their names for a specified year which have been sold.”

Clever naming

This is how clever naming of the SQL unit testing object (ToySalesReport) helps because we are not going to introduce a new object with another name to meet new business requirements rather we are going to keep the same object.

Update ToySalesReport (changing the view into procedure)

We are simply going to convert the SQL view into a SQL stored procedure so that the required sales data for a specified year can be displayed as per business requirements.

Please write and run the following script to update ToySalesReport object to meet the changes in the business requirement:

Update SQL unit test (Year based reporting)

We are updating SQL unit test to meet year based reporting requirement as follows:

Run SQL unit test

We need to run the updated SQL unit test:

TSQLt unit test has also passed after the requirement changed.

Congratulations! The SQL unit test has passed again.

We have not changed the SQL unit testing object name at all rather we updated the SQL unit test after converting the view into a stored procedure and everything seems to be working well.

This is just one simple example of clever naming of SQL unit testing object which lets us easily adapt to changing business requirements with time and if you keep your standard naming like this you can even cover slightly complicated scenarios without ever changing the names of the database or SQL unit testing objects.

Further Reading

I strongly recommend the beginners and database professionals who would like to proceed further with SQL unit testing using tSQLt to go through the following articles:

  1. tSQLt – A Forgotten Treasure in Database Unit Testing
  2. Conventional SQL Unit Testing with tSQLt in Simple Words
  3. Fundamentals of Test-Driven Database Development (TDDD) with tSQLt unit testing
  4. 10 Most Common SQL Unit Testing Mistakes

You can also refer to the below articles, written by my fellow author, Esat Erkec:

  1. SQL unit testing with the tSQLt framework for beginners
  2. How to use fake tables in SQL unit testing?
  3. SQL unit testing best practices

Table of contents

tSQLt – A Forgotten Treasure in Database Unit Testing
Conventional SQL Unit Testing with tSQLt in Simple Words
Fundamentals of Test-Driven Database Development (TDDD) with tSQLt unit testing
10 Most Common SQL Unit Testing Mistakes
Why you should cleverly name Database Objects for SQL Unit Testing
Three Standard SQL Unit Tests you can write against any Stored Procedure
Creating SQL Unit Testing Utility Procedures with tSQLt
SQL Unit Testing Data Warehouse Extracts with tSQLt
The Concept of Test-Driven Data Warehouse Development (TDWD) with tSQLt
Using tSQLt for Test-Driven Data Warehouse Development (TDWD)
SQL database hotfix testing with tSQLt
Test-driven database hotfix development (TDHD) with SQL unit test based framework (tSQLt)
Three ways you can add tSQLt to your SQL database projects
SQL Database unit testing in Azure Data Studio with tSQLt
Haroon Ashraf
Latest posts by Haroon Ashraf (see all)
SQL unit testing, Testing

About 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

168 Views