tSQLt: Unit Testing Has Finally Come to SQL Server

Shannon Lowder

Shannon Lowder

— 2 min read

If you've spent any time in a modern software team, you've heard about unit testing. The Java developers have JUnit. The .NET developers have NUnit and MSTest. Ruby has RSpec. Python has unittest. Meanwhile, SQL Server developers have been sitting on the sidelines of the automated testing revolution, running stored procedures by hand in SSMS and hoping nothing breaks.

That changes with tSQLt.

tSQLt is an open-source unit testing framework for SQL Server, built by Sebastian Meine and Dave Green. It's written in T-SQL and requires only a CLR assembly. If your team can enable CLR on the SQL Server instance, you can start writing tests today.

Why SQL Server Testing Has Always Been Painful

The fundamental problem with testing SQL logic is state. Unit tests are supposed to be isolated — each test should start from a known state and leave no trace when it finishes. Without a framework, you end up writing setup and teardown scripts that are more complex than the thing you're actually testing.

tSQLt solves this with an elegant mechanism: it wraps every test in a transaction, runs the test inside that transaction, and rolls it back when the test completes — regardless of pass or fail. No cleanup scripts. No state leaking between tests. Your database looks exactly the same after running 100 tests as it did before.

Getting tSQLt Installed

Two prerequisites:

-- Enable CLR integration
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

-- Enable TRUSTWORTHY on the target database
ALTER DATABASE YourDatabase SET TRUSTWORTHY ON;

Then download the latest tSQLt release from tsqlt.org, unzip it, and run tSQLt.class.sql against your target database. You get a tSQLt schema with all framework objects installed.

Your First Test

Tests in tSQLt are stored procedures. They live in a schema (tSQLt calls this a "test class"), their names start with the word test, and they call assertion procedures to verify behavior. Say you have a function that classifies account status:

CREATE FUNCTION dbo.GetAccountStatus(@Balance DECIMAL(10,2))
RETURNS NVARCHAR(20)
AS
BEGIN
    RETURN CASE
        WHEN @Balance >= 0          THEN 'GoodStanding'
        WHEN @Balance >= -100       THEN 'Overdue'
        ELSE                             'Delinquent'
    END
END

Create a test class and write your tests:

EXEC tSQLt.NewTestClass 'AccountTests';
GO

CREATE PROCEDURE AccountTests.[test positive balance returns GoodStanding]
AS
BEGIN
    DECLARE @Result NVARCHAR(20) = dbo.GetAccountStatus(100.00);
    EXEC tSQLt.AssertEquals 'GoodStanding', @Result;
END;
GO

CREATE PROCEDURE AccountTests.[test zero balance returns GoodStanding]
AS
BEGIN
    DECLARE @Result NVARCHAR(20) = dbo.GetAccountStatus(0.00);
    EXEC tSQLt.AssertEquals 'GoodStanding', @Result;
END;
GO

CREATE PROCEDURE AccountTests.[test negative balance in range returns Overdue]
AS
BEGIN
    DECLARE @Result NVARCHAR(20) = dbo.GetAccountStatus(-50.00);
    EXEC tSQLt.AssertEquals 'Overdue', @Result;
END;

Run them:

EXEC tSQLt.RunTestClass 'AccountTests';

You get a results table showing which tests passed and which failed, with the failure message telling you exactly what went wrong.

Why This Changes Things

Three tests might seem like a small thing. But now you can add those tests to source control alongside your function definition, run them every time someone touches the code, and catch regressions the moment they happen instead of in production. That's the whole game.

The SQL Server community has been writing application logic in stored procedures for years and deploying it on faith. tSQLt is the first framework I've seen that gives SQL developers the same safety net application developers have had for a decade. I'll be covering it in depth over the coming months. If you're hitting CLR permission issues on install, let me know. As always, I'm here to help.

Read more