Writing & running tSQLt tests

Writing & running tSQLt tests

tSQLt is a unit-testing framework that runs inside SQL Server. Tests live in the database alongside the code they exercise.

How a tSQLt test is structured

• A test class is a schema, created once per file: EXEC tSQLt.NewTestClass '<ClassName>';
• A test is a stored procedure named [<ClassName>].[test <description>] — the test prefix is required.
• Each test runs inside a transaction that tSQLt rolls back automatically: tests never persist data and need no teardown.
• An optional [<ClassName>].SetUp procedure runs before every test in the class.
• Structure each test Assemble → Act → Assert (AAA).

The loop (do this every time)

1. Find or create the test class. A new class is a new file <Name>Tests.class.sql that starts with EXEC tSQLt.NewTestClass '<Name>Tests'; then GO.
2. Write the test procedure with CREATE PROCEDURE <Class>.[test ...] and AAA comments.
3. Isolate dependencies so the test exercises only the code under test:
   • tSQLt.FakeTable — replace a table with an empty, constraint-free copy you populate yourself.
   • tSQLt.FakeFunction — replace a function with a fake of the same signature.
   • tSQLt.SpyProcedure — replace a procedure with a logging stub (optionally still call the original). See reference/isolation-and-mocking.md.
4. Assert with the right assertion (reference/assertions.md). Most common: AssertEquals (scalars), AssertEqualsTable (row data), ExpectException (errors).

Then deploy the class and run it (EXEC tSQLt.Run '<Class>' / EXEC tSQLt.RunAll) — see reference/running-and-results.md. Use whatever deploy/run tooling the project provides; check its README or CLAUDE.md. Iterate until green.

Two habits that keep tests honest:

• Write to the spec, not the code. Assert what the test's name promises (the requirement). A correct test that fails is revealing a bug — don't soften it to match buggy code. See reference/test-conventions.md.
• Fake minimally. Insert only the columns the behaviour reads (a single key column is often enough to materialise a row), and fake only the tables/procedures the code touches. See reference/isolation-and-mocking.md.

A complete example

EXEC tSQLt.NewTestClass 'MyTests';
GO
CREATE PROCEDURE MyTests.[test total sums the order lines]