How to measure SQL Server code coverage

In this article, we will explore what is code coverage and then we will learn, how we can measure SQL Server code coverage.

Code coverage is an indicator that shows how much of the code lines have been covered by the tests. Why this value is important because this value provides us to figure out if the tests cover the code lines extensively. On the other hand, the following question might appear in your mind:

• “Do we really need to measure code coverage?”
• “If you can’t measure it, you can’t improve it”

According to my thought, the answer to this question is absolute “yes” because the developers can evaluate their code by looking to these metrics in terms of the code quality. In some cases, the developers or the program managers appraise the reliability level of the code with code coverage measurement. And also, nowadays it demands highly in the software development ecosystem.

After this brief description of the code coverage, let’s talk about the SQL Server code concept. However, before discussing this concept, we will mention briefly about the SQL Server unit testing. It is clear that the SQL unit testing gains various benefits, for example, the following three clauses straight away come to our mind;

• Improve the T-SQL code quality
• Support to early bug detection
• More reliable T-SQL codes

Therefore, if we are developing a SQL database, and if we also want to achieve the above benefits, there is no doubt we should use the SQL unit testing. At this point, how many lines of the codes are covered by unit tests becomes more important for efficient and advanced SQL unit testing. That’s why, if we want to obtain more qualified and bugless SQL codes, we should measure SQL Server code coverage. SQL Server code coverage concept is based on this essential idea.

• Note: In the further sections of this article, we will work with tSQLt framework therefore If you don’t have enough knowledge about the tSQLt framework, I would suggest to see the SQL unit testing with the tSQLt framework for beginners article. It should be a very good starting point for the newbies in SQL unit testing and tSQLt framework

Overview about SQLCover

SQLCover is a code coverage tool which helps to measure, how many percentages of database objects (stored procedures and functions) code lines have been covered by the tests. Additionally, SQLCover is an open-source project which is written by Ed Elliott (We have to thank Ed Elliott for such a project in an open-source) so that we can enable to change source codes. SQLCover library can be used in the PowerShell scripts or in the .NET projects.

Getting started

Before going through the usage details and demonstration of the SQLCover, we need a sample database which the tSQLt framework has been installed. Manually, we can install the tSQLt framework and manage the SQL unit tests but it is a time-consuming and laborious process. However, we can avoid these types of issues with the help of the ApexSQL Unit Test. ApexSQL Unit Test is a well-designed, effective and handy SQL Server Management Studio add-in solution which helps to install the tSQLt framework with ease and it also allows us to create, manage, organize and run the SQL unit tests. In all SQL unit testing examples, we will work with the ApexSQL Unit Test add-in.

Installing a sample database

In this section, we will prepare a sample database for the demonstration. The name of this database will be ScienceDatabase and this database will contain two scalar-valued functions. These functions make some temperature scale conversions. Through the following script, we can install the sample database:

• Note: Execute this script in your development database servers because it includes dropping a database query

USE master;  
GO
IF DB_ID ( N'ScienceDatabase' ) IS NOT NULL
ALTER DATABASE ScienceDatabase set single_user with rollback immediate
GO
IF DB_ID ( N'ScienceDatabase' ) IS NOT NULL
DROP DATABASE ScienceDatabase ;
GO
CREATE DATABASE ScienceDatabase ;  
GO
USE ScienceDatabase
GO
CREATE   FUNCTION [dbo].[CalcFahtoCelsius](@Fah AS FLOAT) RETURNS FLOAT
AS
BEGIN
DECLARE @Cel AS FLOAT
SELECT @Cel = ROUND((@Fah- 32)/1.8,0)
RETURN @Cel
END
GO
CREATE   FUNCTION [dbo].[CalcFahtoKelvin](@Fah AS FLOAT) RETURNS FLOAT
AS
BEGIN
DECLARE @Kel AS FLOAT
SELECT @Kel = ROUND(((@Fah+ 459.67)*5)/9,2)
RETURN @Kel
END

Installing the tSQLt framework

ApexSQL Unit Test offers 3 different options to install the tSQLt framework. Now let’s learn, how we can install the tSQLt framework with the help of the ApexSQL Unit Test easily.

Launch the SQL Server Management Studio and right-click in ScienceDatabase and then choose to Install tSQLt option in the Unit tests menu.

In the Install tSQLt window, we will select the Built-in tSQLt installation type and click OK.

And also, we can use the following tSQLt framework installation methods in the ApexSQL Unit Test.

• File System
• Web

We will allow enabling the following database settings;

• TRUSTWORTH ON
• Enable SQL CLR

In the last step, ApexSQL Unit Test gives information about the tSQLt installation result.

As you can see that we installed the tSQLt framework very easily in the two-step.

Creating and running the unit test

The ScienceDatabase does not involve any unit test so we will create a new unit test.

When we again right-click over the ScienceDatabase, the New test option appears in the Unit tests menu. We will choose the New test and then start to create a unit test. In the New test window, we can create a new test class or choose the previously created test classes in the drop-down menu.

We will click the New class button and then create a new test class. We should give a name to the test class and then click OK so that we created the test class.

After the creation of the test class, we will start to create a new unit test. Give a name to the unit test and then click OK.

After that, ApexSQL Unit Test creates a new unit test which we named it. This unit test stored procedure is shown in the SSMS query editor automatically so that we can start to code the unit test. Through the following query, we can create the unit test.

USE [ScienceDatabase]
GO
-- =============================================
-- Author:    SQLShack.com
-- Create date: 24.07.2019
-- Description: Testing the CalcFahtoCelsius scalar-valued function
-- =============================================
ALTER PROCEDURE [SampleTestClass].[test fnCalcFahtoCelsius_ExpectedRightCelciusVal]
AS
BEGIN
 SET NOCOUNT ON;
 DECLARE @Expected AS FLOAT
 DECLARE @Actual AS FLOAT
 SET @Expected = 149
 SELECT @Actual = dbo.CalcFahtoCelsius(300)
 EXEC tSQLt.AssertEquals @Expected , @Actual
END

We will click the Unit Test explorer to run the unit test.

In the Unit test explorer window, we can run individual unit tests.

At the same time, we can run the whole unit tests which are contained by the test class.

Now we will run the fnCalcFahtoCelsius_ExpectedRightCelciusVal unit test and analyze the result of the unit test in the result panel.

The above result screen image explains that the fnCalcFahtoCelsius_ExpectedRightCelciusVal test has passed.

As you see, with the help of the ApexSQL Unit Test we avoided various manual operations when we wanted to create and run the unit tests. ApexSQL Unit Test offers the following practical experience to us;

• To create a new test class
• To create and edit a unit test
• Run the individual unit test
• To run whole unit tests under the test class or database
• Easily understandable result panel

Measuring the SQL Server code coverage

At the beginning of this article, we mentioned SQLCover and now we reinforce this information with practical examples. As the first step of this demonstration, we download the required tools from the SQLCover GitHub repository. We will create a very simple PowerShell script which is using the SQLCover library so that we can create an HTML report. This report will offer the detailed measurement result of the SQL Server code coverage for the ScienceDatabase database.

At first, we will open a PowerShell ISE for scripting and save it to the same folder with the SQLCover.dll and SQLCover.ps1 as RunSQLCover.ps1. Then through the following PowerShell script, we can generate the HTML report.

• Note: You should configure the connection string according to your database connections.

 . .\SQLCover.ps1
$SQLCoverScriptDir = Split-Path $script:MyInvocation.MyCommand.Path
$SQLCoverDllFullPath =   $SQLCoverScriptDir  + "\SQLCover.dll"
$result = Get-CoverTSql   $SQLCoverDllFullPath "server= localhost;User Id=sa; Password=yourpass ;initial catalog=ScienceDatabase" "ScienceDatabase" "EXEC tSQLt.RunAll"
Export-Html $result  $SQLCoverScriptDir

Now, we will tackle the PowerShell script line by line

.\SQLCover.ps1
$SQLCoverScriptDir = Split-Path $script:MyInvocation.MyCommand.Path
$SQLCoverDllFullPath =   $SQLCoverScriptDir  + "\SQLCover.dll"

In the above codes, we specified the SQLCover.dll and SQLCover.ps1 script paths.

$result = Get-CoverTSql   $SQLCoverDllFullPath "server= localhost;User Id=sa; Password=yourpass ;initial catalog=ScienceDatabase" "ScienceDatabase" "EXEC tSQLt.RunAll"

In the above code block, we defined the connection string of the server and database name. Then we set the unit test query. In our script, we specified to run all unit tests.

Export-Html $result  $SQLCoverScriptDir

The above code specifies, where the HTML report will be created.

The HTML base coverage report is saved into the specified path when we run the PowerShell script. Lets’s open this report and discuss it.

As we can see that, the ScienceDatabase SQL Server code coverage measurement value is 50% because SQLCover found 4 total executable code lines but only 2 executable code lines have been covered by the unit tests.

On the other hand, if we analyze the CalcFahtoCelcius scalar-valued function, it’s measurement value is 100% because of the whole executable statements have been covered by the unit test.

Now, we will overlearn this idea with a more complicated example. At first, we will make some changes to the CalcFahtoCelsius function. These changes consist of some ‘if’ statements, and also it includes comment lines that point to the numerated executable code lines. Let’s run the following query and alter the CalcFahtoCelsius scalar-valued function. Then we will re-generate the HTML report with the help of the same PowerShell script.

USE [ScienceDatabase]
GO
ALTER   FUNCTION [dbo].[CalcFahtoCelsius](@Fah AS FLOAT) RETURNS FLOAT
AS
BEGIN
DECLARE @Year AS INT 
DECLARE @Cel AS FLOAT=0
SELECT @Year = DATEPART(YEAR,GETDATE()) --Statement  1
IF @Year =2018 --Statement 2
BEGIN
SELECT @Cel=0 --Statement 3
END
IF @Year =2017 --Statement 4
BEGIN
SELECT @Cel=0 --Statement 5
END

IF @Year =2016 --Statement 6
BEGIN
SELECT @Cel=0 --Statement 7
END

IF @Year =2019 --Statement 8
BEGIN
SELECT @Cel = ROUND((@Fah- 32)/1.8,0) --Statement 9
END

RETURN @Cel --Statement 10
END

The above image explains everything very clearly to figure out the SQL Server code coverage measurement methodology and which statements to be considered in the calculations.

At the same time, the report is indicating some code lines highlighted in green. It specifies, which code lines have been executed during the unit test execution period. We should notice one thing about SQLCover, it does not reckon some codes during the SQL Server code coverage measurement. T-SQL statements like BEGIN, DECLARE, etc are not considered by SQLCover because these codes actually do nothing so it can be eliminated in the SQL Server code coverage measurement.

Conclusion

In this article, we learned the adaptation of the code coverage measurement approach to SQL Server. If we use SQL unit testing and SQL Server code coverage at the same time, this combo usage will improve our code quality and reliability. Also, we can measure how many lines of the code exercised by the SQL Server unit tests. As briefly, we can repeat the idea “If you can’t measure it, you can’t improve it”.