Getting Started with tSQLt: Your First Passing Test

tSQLt is an open-source unit testing framework for SQL Server. Unlike application code where testing frameworks are well established, database testing has historically been an afterthought. tSQLt changes that by giving you a structured way to write, run, and assert on T-SQL logic without touching production data.

Prerequisites

• SQL Server 2016 or later (Express works fine for local dev)
• A database to install tSQLt into (use a dev/test database, not production)
• SQLCMD or SQL Server Management Studio (SSMS)

Step 1: Download and Install tSQLt

Download the latest tSQLt release from tSQLt.org. The download includes a single SQL script: tSQLt.class.sql. Run this script against your target database. The installation enables CLR on the server and creates the tSQLt schema with all its helper procedures.

-- Enable CLR (required for tSQLt)
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

-- Run this from the tSQLt download
:r tSQLt.class.sql

Step 2: Create a Test Class

In tSQLt, tests are organized into test classes, which are just SQL Server schemas. You create a test class using tSQLt.NewTestClass. This sets up the schema and registers it with the framework.

EXEC tSQLt.NewTestClass @ClassName = 'CustomerTests';

Step 3: Write Your First Test

Each test is a stored procedure inside your test class schema. The procedure name must start with "test". Inside the procedure, you call the code under test, then use tSQLt.AssertEquals (or other assertion procedures) to verify the result.

CREATE OR ALTER PROCEDURE [CustomerTests].[test that GetCustomer returns correct name]
AS
BEGIN
  -- Arrange
  INSERT INTO dbo.Customers (CustomerID, Name)
  VALUES (1, 'Alice');

  -- Act
  DECLARE @result NVARCHAR(100);
  EXEC @result = dbo.GetCustomerName @CustomerID = 1;

  -- Assert
  EXEC tSQLt.AssertEquals @Expected = 'Alice', @Actual = @result;
END;

Step 4: Run Your Tests

You can run all tests in a class, or run all tests across the entire database. tSQLt wraps each test in a transaction and rolls it back after, so your data is never permanently modified.

-- Run all tests in a class
EXEC tSQLt.Run @TestName = 'CustomerTests';

-- Run all tests in the database
EXEC tSQLt.RunAll;

What's Next

Once you have your first test passing, the natural next step is to learn about FakeTable and SpyProcedure — the two features that make isolated testing practical on real-world databases. These let you test stored procedures without depending on actual data or external procedure calls.