SQL Unit Testing Using tSQLt: Part 5


Welcome back friends!

During the past four posts, we have covered the fundamental concepts regarding tSQLt, look at how far we’ve come!. So far, everything we have done has been within SSMS using the open source framework that is tSQLt. In this post, we’re going to take a look at some third party tooling available, harnessing the power of tSQLt.


Redgate SQL Test

I’ve been a strong advocate for Redgate tools for years. They have simplified and enhanced the life of many a DBA over the years, including myself. Redgate SQL Test is no exception.

Redgate SQL Test provides a nice, integrated GUI into SSMS for all things unit testing. Some of the key features include:

  • Uses the tSQLt Framework
  • Comes with built-in, pre-written COP tests to check for general best practices
  • Enables one-click execution of unit tests at varying levels (database, schema, unit test)
  • Integrates with SQL Cover to provide code coverage analysis
  • Provides a graphical view of test classes and procedures

Installing Redgate SQL Test is very easy to do and can be downloaded here: https://www.red-gate.com/products/sql-test/
A free 28 day trial is available, and I highly recommend giving it a try.


Today’s Agenda

In today’s post, we’ll explore key features of Redgate SQL Test and how they can enhance SQL unit testing. You guessed it, we’re diving into a demo.


Setup - The Procedure

Once again, we will be using the StackOverflow2010 database and we have a new procedure for today’s demo, called dbo.GetUsersWithMostBadges.

An overview of what it does:

  • Identifies the top three users by badges earned using a CTE
  • Joins the CTE to the users table to retrieve their Display Names
  • Checks the @featureflag variable and creates a temporary table when necessary

Here is the code:

CREATE OR ALTER PROCEDURE [dbo].[GetUsersWithMostBadges]
	@featureflag BIT
AS
BEGIN

;WITH GetTop3 AS
(
	SELECT TOP (3)
		b.UserId,
		COUNT(b.Name) AS [NumberOfBadges]
	FROM dbo.Badges b
	GROUP BY b.UserId
	ORDER BY COUNT(b.Name) DESC
)

SELECT 
	u.DisplayName,
	t.NumberOfBadges
FROM GetTop3 t
INNER JOIN dbo.Users u ON t.UserID = u.Id

IF @featureflag	= 1
BEGIN
	CREATE TABLE #logging (id INT)
END

END
GO

Setup - The Unit Test

For the accompanying unit test, we have [tests].[test GetUsersWithMostBadges]. This test verifies that the stored procedure returns the top three users based on badge count correctly:

CREATE OR ALTER PROCEDURE [tests].[test GetUsersWithMostBadges]
AS
BEGIN

/* ASSEMBLE STAGE */

EXEC tSQLt.FakeTable 'dbo.Badges'

INSERT INTO dbo.Badges
	(UserId, Name)
VALUES
	(10,	'Nice Answer'),
	(8,		'Nice Answer'),
	(10,	'Guru'),
	(10,	'Nice Answer'),
	(10,	'Nice Answer'),
	(10,	'.net'),
	(10,	'Good Answer'),
	(8,		'c#'),
	(8,		'Stellar Question'),
	(10,	'.net'),
	(10,	'.net'),
	(100,	'agile'),
	(8,		'c#'),
	(10,	'.net'),
	(6,		'vb.net'),
	(6,		'visual-studio'),
	(6,		'vb.net')


EXEC tSQLt.FakeTable 'dbo.Users'

INSERT INTO dbo.Users
	(Id, DisplayName)
VALUES
	(10,	'Jon Skeet'),
	(8,		'Marc Gravell'),
	(6,		'JaredPar'),
	(100,	'Lee Brownhill')


CREATE TABLE dbo.Expected
(DisplayName NVARCHAR(40),NumberOfBadges INT)


CREATE TABLE dbo.Actual
(DisplayName NVARCHAR(40),NumberOfBadges INT)


INSERT INTO dbo.Expected
(DisplayName,NumberOfBadges)
VALUES
	('Jon Skeet',		9),
	('Marc Gravell',	4),
	('JaredPar',		3)

/* ACT STAGE */

INSERT INTO dbo.Actual
	EXEC dbo.GetUsersWithMostBadges @featureflag = 0


/* ASSERT STAGE */

EXEC tSQLt.AssertEqualsTable
	@Expected = 'dbo.Expected',
	@Actual = 'dbo.Actual',
	@FailMsg = 'Our test failed!'


Using Redgate SQL Test - Navigation

Now that we have both our Stored Procedure and Unit Test, we can open up Redgate SQL Test within SSMS:

sql test


In my environment, this opens a window that looks like this:

sql test

  1. Our database, StackOverflow2010, contains our test classes.
  2. Our schemas contain unit tests.
  3. Our unit tests reside within the ’tests’ schema.


The best part? We can run our tests with a single click! Even better, we can run tests at different scopes within the database.

To do this, simply use the right-click context menu:

sql test

Using this process, we can:

  1. Run all tests within the database.
  2. Run all tests within the ’tests’ schema.
  3. Run individual unit tests.


Using Redgate SQL Test - Running Tests

As shown, the GUI allows us to run unit tests easily with a single click. After running the GetUsersWithMostBadges test, we see the following:

sql test

A green tick confirms the unit test has passed. This process mirrors how we’ve been executing tests previously:

EXEC tsqlt.Run @TestName = '[tests].[test GetUsersWithMostBadges]'

The Test Output window provides additional details, including execution results and diagnostic messages:

sql test



Using Redgate SQL Test - Code Coverage

Next, I’ll highlight one of my favorite features in Redgate SQL Test: code coverage. “Code Coverage” as a concept within unit testing is exactly what you probably think it is.

“How much of my code is covered by my unit test?”

This feature of SQL Test is very powerful and gives us a fantastic tool to use when we want to ensure all of our code is ‘covered’ by unit tests. Let’s run through an example.

Let’s run our unit test again and navigate to the ‘Code Coverage’ tab in SQL Test:

sql test

Before moving on, I want to highlight a couple of key points from the above screenshot, within the Code Coverage tab:

  • Under the Object Name column header, we can see our current Stored Procedure - dbo.GetUsersWithMostBadges. All of the other entries are items that exist within this database.
  • This procedure has three statements, and two of them are covered by our unit test (remember this, dear reader)
  • This gives us 66.67% code coverage

Clicking the object name link takes us to the code for the underlying procedure:

sql test

As shown above, the lines of code in green are covered by our associated unit test. Variable declarations and BEGIN/END keywords are intentionally excluded, as tSQLt does not consider them. The green-highlighted lines indicate code executed as part of the associated unit test.

However, we can see that one line - CREATE TABLE #logging (id INT) - is not covered by the associated unit test. This aligns with the previous screenshot, where SQL Test showed that three statements exist, but only two are covered by our test.

As you may have noticed, the missing ‘CREATE TABLE’ statement isn’t executed because our unit test runs the procedure with @featureflag = 0:

/* ACT STAGE */

INSERT INTO dbo.Actual
	EXEC dbo.GetUsersWithMostBadges @featureflag = 0

So we need to create a new version of the unit test that calls the procedure with @featureflag = 1.

Let’s create and deploy a new unit test named ’test_GetUsersWithMostBadges2’. In the Act stage, we’ll call the procedure with @featureflag = 1.
Here’s a snippet of the new unit test:

/* ACT STAGE */

INSERT INTO dbo.Actual
	EXEC dbo.GetUsersWithMostBadges @featureflag = 1

Once deployed, we can refresh the ’tests’ schema in SQL Test to view our new test GetUsersWithMostBadges2:

sql test


Let’s execute the new unit test. Once it’s run, within the Code Coverage tab, we can see that all three statements are now covered, achieving 100% coverage:

sql test


Clicking the linked stored procedure name takes us to the corresponding procedure code, showing that the previously missing coverage is now resolved:

sql test



Recap

Today, we covered Redgate SQL Test (pun totally intended). We explored a small yet powerful part of this tool’s functionality. As with most Redgate tooling, it really makes life easier. With its intuitive GUI, we can quickly run tests, assess coverage, and identify gaps where tests are missing.

That brings us to the end of this series, for now. I do hope you have enjoyed this series as much as I have had creating it. It’s been great fun getting back into Unit Testing within SQL Server.

See you in the next post!


If you’ve found this post helpful or would just like to support the work I do here, consider buying me a coffee.

Buy Me A Coffee