Database Unit Testing With tSQLt: What SSDT Made Possible

Shannon Lowder

Shannon Lowder

— 3 min read

Nobody tested their stored procedures in 2010. I mean that almost literally — the pattern was: write the procedure, run it manually with a few sample inputs, see if it does approximately what you wanted. Automated tests for database logic were theoretically possible but practically nonexistent in most shops. SSDT and the schema-in-source-control model changed the conditions that made testing practical, and tSQLt gave us the framework to actually do it.

Why Testing Was Hard Before

Database unit tests require a controlled environment. You need a database with a known state, you run the thing you're testing, and you verify the result. The problem: in 2010, setting up a fresh database for each test run was manual work. You couldn't easily spin up a clean copy of the schema. You couldn't isolate test data from production data. Test runs interfered with each other if they shared a database.

SSDT's dacpac model solved the environment problem. A CI pipeline could deploy a fresh database from the dacpac in under a minute, run all the tests, and drop the database. Clean slate, every run, automated. The infrastructure that made repeatable deployment easy was the same infrastructure that made automated testing practical.

tSQLt in Practice

tSQLt is a SQL Server testing framework that runs entirely inside SQL Server. Tests are stored procedures in a test schema. You install tSQLt as a CLR assembly, create test classes (schemas), write test procedures, and run them with EXEC tSQLt.RunAll.

-- Install tSQLt in your test database
-- (download tSQLt.class.sql from tSQLt.org and run it)

-- Create a test class
EXEC tSQLt.NewTestClass 'CustomerTests';
GO

-- Write a test
CREATE PROCEDURE CustomerTests.[test that CreateCustomer assigns a unique ID]
AS
BEGIN
    -- Arrange: set up test data
    EXEC tSQLt.FakeTable 'dbo.Customers'; -- creates an empty copy, ignoring FK constraints
    
    -- Act: call the procedure under test
    DECLARE @CustomerId INT;
    EXEC dbo.usp_CreateCustomer 
        @CustomerName = 'Leia Organa',
        @Email = 'leia@alderaan.gov',
        @CustomerId = @CustomerId OUTPUT;
    
    -- Assert: verify the result
    DECLARE @ActualId INT;
    SELECT @ActualId = CustomerId FROM dbo.Customers WHERE Email = 'leia@alderaan.gov';
    
    EXEC tSQLt.AssertEquals @Expected = @CustomerId, @Actual = @ActualId;
END;
GO

-- Run all tests
EXEC tSQLt.RunAll;

FakeTable: The Key to Isolation

tSQLt.FakeTable is the mechanism that makes test isolation practical. It replaces the real table with an empty version that has the same column structure but none of the constraints, foreign keys, or triggers. Your test can insert whatever it needs without worrying about referential integrity to tables the test isn't testing. After the test runs, tSQLt rolls back everything — the original table is restored.

This isolation model means tests don't depend on each other. You can run them in any order. A failed test that inserted bad data doesn't corrupt the next test's starting state. Clean slate is guaranteed by the framework.

What's Worth Testing

Not everything needs a tSQLt test. Tables and views with no business logic: not worth testing. The test would just verify that SQL Server created a table when you said CREATE TABLE. What's worth testing:

  • Stored procedures with conditional logic — especially branching logic that handles edge cases. Test the happy path and each failure mode.
  • Functions with complex calculations — scalar functions that implement business rules, date calculations, pricing logic. These are pure-function tests: given these inputs, assert this output.
  • Triggers with business rules — if a trigger enforces an invariant that isn't captured by a constraint, test it.
  • Complex queries used as views or in reports — verify the aggregation logic with known test data produces expected results.

The ROI Conversation

The question I always got when recommending tSQLt: "Is writing tests actually worth the time?" For simple CRUD procedures: probably not. For stored procedures that implement pricing rules, commission calculations, or any business logic that the company depends on: absolutely yes.

A stored procedure that calculates a financial amount has a test case for every edge case your business has encountered. When someone modifies that procedure — correctly or incorrectly — the tests tell you immediately whether the modification broke any of those edge cases. Without tests, you find out when finance calls because the quarterly numbers are wrong. The ROI math isn't close. As always, I'm here to help.

Read more