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:

 1CREATE OR ALTER PROCEDURE [dbo].[GetUsersWithMostBadges]
 2	@featureflag BIT
 3AS
 4BEGIN
 5
 6;WITH GetTop3 AS
 7(
 8	SELECT TOP (3)
 9		b.UserId,
10		COUNT(b.Name) AS [NumberOfBadges]
11	FROM dbo.Badges b
12	GROUP BY b.UserId
13	ORDER BY COUNT(b.Name) DESC
14)
15
16SELECT 
17	u.DisplayName,
18	t.NumberOfBadges
19FROM GetTop3 t
20INNER JOIN dbo.Users u ON t.UserID = u.Id
21
22IF @featureflag	= 1
23BEGIN
24	CREATE TABLE #logging (id INT)
25END
26
27END
28GO

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:

 1CREATE OR ALTER PROCEDURE [tests].[test GetUsersWithMostBadges]
 2AS
 3BEGIN
 4
 5/* ASSEMBLE STAGE */
 6
 7EXEC tSQLt.FakeTable 'dbo.Badges'
 8
 9INSERT INTO dbo.Badges
10	(UserId, Name)
11VALUES
12	(10,	'Nice Answer'),
13	(8,		'Nice Answer'),
14	(10,	'Guru'),
15	(10,	'Nice Answer'),
16	(10,	'Nice Answer'),
17	(10,	'.net'),
18	(10,	'Good Answer'),
19	(8,		'c#'),
20	(8,		'Stellar Question'),
21	(10,	'.net'),
22	(10,	'.net'),
23	(100,	'agile'),
24	(8,		'c#'),
25	(10,	'.net'),
26	(6,		'vb.net'),
27	(6,		'visual-studio'),
28	(6,		'vb.net')
29
30
31EXEC tSQLt.FakeTable 'dbo.Users'
32
33INSERT INTO dbo.Users
34	(Id, DisplayName)
35VALUES
36	(10,	'Jon Skeet'),
37	(8,		'Marc Gravell'),
38	(6,		'JaredPar'),
39	(100,	'Lee Brownhill')
40
41
42CREATE TABLE dbo.Expected
43(DisplayName NVARCHAR(40),NumberOfBadges INT)
44
45
46CREATE TABLE dbo.Actual
47(DisplayName NVARCHAR(40),NumberOfBadges INT)
48
49
50INSERT INTO dbo.Expected
51(DisplayName,NumberOfBadges)
52VALUES
53	('Jon Skeet',		9),
54	('Marc Gravell',	4),
55	('JaredPar',		3)
56
57/* ACT STAGE */
58
59INSERT INTO dbo.Actual
60	EXEC dbo.GetUsersWithMostBadges @featureflag = 0
61
62
63/* ASSERT STAGE */
64
65EXEC tSQLt.AssertEqualsTable
66	@Expected = 'dbo.Expected',
67	@Actual = 'dbo.Actual',
68	@FailMsg = 'Our test failed!'

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

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

Our database, StackOverflow2010, contains our test classes.
Our schemas contain unit tests.
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:

Using this process, we can:

Run all tests within the database.
Run all tests within the ’tests’ schema.
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:

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

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

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

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:

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:

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:

1/* ACT STAGE */
2
3INSERT INTO dbo.Actual
4	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:

1/* ACT STAGE */
2
3INSERT INTO dbo.Actual
4	EXEC dbo.GetUsersWithMostBadges @featureflag = 1

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

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:

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

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!