This article talks about the three standard SQL unit tests which can be written against any stored procedure ultimately becoming SQL unit testing object to meet internal or external business specification.
This will also shed light on the importance of standardizing your SQL unit testing arsenal to an extent that it becomes quicker to get your SQL unit testing job done against your desired database.
Additionally, this article emphasizes database developers and professionals to think about having stored SQL unit tests templates ready to be used against any existing or upcoming databases.
Pre-requisites
Let us first quickly go through the pre-requisites of the article so that the concepts and walkthroughs in this article can be easily understood by the readers.
T-SQL skills
The article assumes that the readers are well familiar with T-SQL scripting and are comfortable to write and run SQL queries against the databases.
SQL unit testing concepts
It is also desired that the readers have basic SQL unit testing concepts along with the know-how of any of the two database unit testing practices:
- Conventional database unit testing
- Test-driven database development (TDDD)
To get a quick understanding of these SQL unit testing practices please refer to the following articles:
- Conventional SQL Unit Testing with tSQLt in Simple Words
- Fundamentals of Test-Driven Database Development (TDDD) with tSQLt unit testing
TSQLt Familiarity
This article also assumes that the readers are well familiar with tSQLt, one of the most famous SQL unit testing frameworks, which by default (design) supports test-driven database development.
If you have not got enough understanding of tSQLt then please refer to the following articles:
- tSQLt – A Forgotten Treasure in Database Unit Testing
- Why you should cleverly name Database Objects for SQL Unit Testing
Setup Sample Database
Setup a sample database named StandardTSQLT by running the following SQL script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
CREATE DATABASE StandardTSQLT GO USE StandardTSQLT CREATE TABLE [dbo].[TheTable] ( [TemplateId] INT NOT NULL IDENTITY(1,1), [Name] VARCHAR(60) NOT NULL, [Detail] VARCHAR(200) NULL, CONSTRAINT [PK_TestTemplate] PRIMARY KEY ([TemplateId]) ); GO CREATE PROCEDURE [dbo].[TheObject] @Name VARCHAR(60), @Detail VARCHAR(200) AS INSERT INTO TheTable (Name,Detail) VALUES (@Name,@Detail) GO |
Run the script and view the sample database and its objects:
Setup tSQLt Framework
Run tSQLt.class.sql (downloaded from tSQLt.org) script against the sample database to add tSQLt framework for SQL unit testing:
Create SQL unit test class template
First of all, create a general test class called ObjectTests in the sample database StandardTSQLT as follows:
|
1 2 3 4 5 6 7 8 |
USE StandardTSQLT GO -- Creating unit test class ObjectTests CREATE SCHEMA [ObjectTests] Authorization dbo GO EXECUTE sp_addextendedproperty @name='tSQLt.TestClass',@value=1,@level0type='SCHEMA',@level0name='ObjectTests' |
Please note that this article presents a new approach inspired by the SQL unit testing guidelines by Dave Green.
Why Standard SQL unit tests
It is not odd to think that do we really need standard SQL unit tests while things change frequently in the database and SQL unit testing realm?
Yes, it is good to have standard SQL unit tests so that they can not only save time but also help to standardize our overall SQL database development and testing strategy contributing to streamlining database lifecycle management process.
Typical SQL unit testing scenario
If we closely look at a typical SQL database development and SQL unit testing scenario then we can easily understand the importance of having standard SQL unit tests beforehand as we move from database to database.
A typical SQL unit testing scenario is as follows:
- Create a database object to meet some business requirement
- Create a SQL unit test to check the database object
- Run SQL unit test to check the database object does the job or not
- If the test is passed then move on to the next SQL unit test
How many times you have to repeat this process and in fact for one database you may create several database objects which must be unit tested to ensure that they do the job they were created for.
Let us suppose you already have some SQL unit tests in mind or on paper (as a start-up) then would not that be easy to straightaway create or reuse those standard SQL unit tests for any new object you work on.
However, please bear in mind that it is not always possible to have standard SQL unit tests that fit every case.
Stored Procedure and Standard unit tests
Another good example to understand the need for standard SQL unit tests is to look at a stored procedure.
If you reverse engineer a stored procedure it is typically a set of SQL scripts that are frequently used to serve the purpose.
So, if we can wrap a set of repeatable SQL scripts into a stored procedure to avoid repetition and standardize the process then why not we can have standard SQL unit tests to be applicable to most of the SQL unit testing objects.
Let us now focus on the three standard SQL unit tests which you can write against almost any stored procedure.
1 – Test to check object exists
The first standard SQL unit test against any stored procedure is going to check whether it has been created or not.
Please remember the order in which database object and its SQL unit test is created may vary depending on the SQL unit testing pattern in use.
Conventional SQL unit testing
In this pattern, database objects are created first and their SQL unit tests are written next.
So the order is as follows:
- You create a SQL unit testing object which is stored procedure to meet business specification
- You create a SQL unit test to check if it exists or not
Obviously, it exists so what is the point of creating the SQL unit test to check object exists or not.
Actually, this is going to make sure the object has not been accidentally or purposely deleted (due to a conflicting requirement). This is somewhat like a schema-bound view which we can call SQL unit test bound object, a new term I am coining for the first time.
Create SQL unit test Object Exists Template
Next, create SQL unit test object exists template as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
--Creating object exists test template CREATE PROCEDURE ObjectTests.[Test to check Object exists] AS BEGIN --Assemble --Act --Assert EXEC tSQLt.AssertObjectExists @ObjectName = N'TheObject' END; GO |
Dry Run SQL unit test
|
1 2 |
--Run tSQLt Tests EXEC tSQLt.RunAll |
So, the first most common SQL unit test is to check if the object of interest exists or not is complete now. We have just created this SQL unit test in the form a template that is going to be used afterward.
2 – Test to check object has normal output against normal input data
The next most desired SQL unit test is to check if object outputs normally when given normal input data.
Normal Input Data
Normal input data is any acceptable input given to SQL unit testing object.
For example, if your object under test is a stored procedure which accepts Name parameter then any valid name or string supplied to the stored procedure during the SQL unit test is considered normal input data.
Normal Output Data
This means if an object has been given a normal input we expect it to give us the normal or expected output.
For example if a stored procedure accepts a name and displays the list of authors having the same name then this is normal output data.
Alternatively, if a stored procedure accepts a name and adds it to a table then getting the contents of that table is considered normal output provided it contains the expected data inserted by stored procedure.
In other words, this SQL unit test ensures that the stored procedure (if this is the object under test), when given acceptable input, produces acceptable output.
Create Normal Output When Normal Input Template
Now create this SQL unit test template as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
--Create object normal output for normal input template CREATE PROCEDURE [ObjectTests].[test to check Object ouputs normally when given normal input] AS -- Assemble EXEC tSQLt.FakeTable @TableName='dbo.TheTable',@Identity=1-- Fake related table CREATE TABLE [ObjectTests].[Expected] -- Create expected table ( [TemplateId] INT, [Name] VARCHAR(60) NOT NULL, [Detail] VARCHAR(200) NULL, ) INSERT INTO ObjectTests.Expected -- Insert data into exepcted table (TemplateId,Name,Detail) VALUES (1,'Object Exists','Test to check object exists') -- Act EXEC dbo.TheObject 'Object Exists','Test to check object exists' -- Run procedure SELECT * INTO ObjectTests.Actual FROM dbo.TheTable -- Put the output into Actual table -- Assert (compare expected table with actual table results) EXEC tSQLt.AssertEqualsTable @Expected='ObjectTests.Expected',@Actual='ObjectTests.Actual' |
Dry Run SQL unit tests
|
1 2 |
--Run tSQLt Tests EXEC tSQLt.RunAll |
3 – Test to check object has abnormal output against abnormal input data
This is also one of the most widely used SQL unit tests since it checks for abnormal input and expects abnormal output.
Abnormal Input Data
Abnormal input data can be of different forms including no data at all to check the behavior of the object.
Abnormal Output Data
Abnormal output data can also be of various types including no output based on no data.
Please create Abnormal Output against Abnormal Input Template
Yes, I would like you to give it a try and let me know how far you can go. The clue is to consider no input and no output abnormality when writing your SQL unit test template.
Final Word and Further Reading
In this article, we have successfully created two general SQL unit tests templates and purposely left the third one for the readers to try to implement.
However, all of these SQL unit testing templates can now test any general stored procedure.
Please go through the following articles to see these templates in action:
- Why you should cleverly name Database Objects for SQL Unit Testing
- Fundamentals of Test-Driven Database Development (TDDD) with tSQLt unit testing
- Conventional SQL Unit Testing with tSQLt in Simple Words
Table of contents
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
- 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