tSQLt Test Organization: Structuring Your Suite as It Grows

Shannon Lowder

Shannon Lowder

— 2 min read

A tSQLt suite with 10 tests is easy to manage. A suite with 200 tests is a different problem. The test class structure that felt obvious when you started starts to break down: tests that belong together aren't, test names that made sense in isolation are ambiguous at scale, and running the full suite takes long enough that developers start skipping it.

Here's how I structure tSQLt suites once they grow past the early stage.

Test Class Naming: Use the Full 128 Characters

tSQLt test classes are schemas. SQL Server allows schema names up to 128 characters. Use them. Short names like OrdTests or CustTests look fine in a two-class suite; they become ambiguous soup at 20 classes.

I use a pattern that mirrors the object being tested:

-- Pattern: [ObjectType]_[ObjectName]_Tests
EXEC tSQLt.NewTestClass 'Proc_GetCustomerOrders_Tests';
EXEC tSQLt.NewTestClass 'Trigger_Orders_AfterUpdate_Tests';
EXEC tSQLt.NewTestClass 'View_ActiveCustomers_Tests';
EXEC tSQLt.NewTestClass 'Func_CalculateLateFee_Tests';

When a test fails in the CI output, the class name tells you which production object to look at before you open a single file.

Test Procedure Names: Write the Specification

tSQLt requires procedure names to start with the word "test." Beyond that, the name should be the specification of what the test verifies — readable without opening the body:

-- Bad: requires reading the body to understand intent
CREATE PROCEDURE Proc_GetCustomerOrders_Tests.[test1]
CREATE PROCEDURE Proc_GetCustomerOrders_Tests.[test date filter]

-- Good: the name is the specification
CREATE PROCEDURE Proc_GetCustomerOrders_Tests.[test returns only orders in date range]
CREATE PROCEDURE Proc_GetCustomerOrders_Tests.[test excludes cancelled orders]
CREATE PROCEDURE Proc_GetCustomerOrders_Tests.[test returns empty set when no orders exist for customer]

If you can't write a name that concise, the test is probably doing too many things.

One Test Class Per Production Object

Don't group unrelated procedures into a single test class. One production object, one test class. This makes partial runs natural during development:

-- Working on GetCustomerOrders — run only its tests
EXEC tSQLt.RunTestClass 'Proc_GetCustomerOrders_Tests';

-- Ready to commit — verify nothing else broke
EXEC tSQLt.RunAll;

The SetUp Hook

When multiple tests in a class need the same starting state — the same fake tables, the same base rows — tSQLt provides a SetUp procedure hook. If a test class contains a procedure named exactly SetUp, tSQLt runs it before each test in the class:

CREATE PROCEDURE Proc_GetCustomerOrders_Tests.SetUp
AS
BEGIN
    EXEC tSQLt.FakeTable 'dbo.Orders';
    EXEC tSQLt.FakeTable 'dbo.Customers';

    -- Base data common to most tests in this class
    INSERT INTO dbo.Customers (CustomerID, Name, IsActive)
    VALUES (101, 'Han Solo', 1);
END;

Individual tests then insert only the specific rows their scenario needs, on top of the base setup. This eliminates copy-paste setup blocks that accumulate as the suite grows and become a maintenance liability every time the underlying schema changes.

Source Control Layout

In your SSDT test project, mirror the test class structure in the folder hierarchy:

YourDatabase.Tests/
  Tests/
    Proc_GetCustomerOrders_Tests/
      SetUp.sql
      test returns only orders in date range.sql
      test excludes cancelled orders.sql
    Proc_UpdateOrderStatus_Tests/
      SetUp.sql
      test sends notification on shipped.sql
      test does not notify on non-shipped status.sql

One file per test procedure. When reviewing a pull request, the changed files tell you immediately which tests were added, modified, or removed alongside the production code changes. A procedure change with no corresponding test change is visible in the diff.

The Discipline That Separates a Useful Suite from a Pile of Tests

Organization doesn't make the tests correct — that's on you. But it determines whether the suite is usable when something breaks. A well-organized suite gives you a fast path from "build failed" to "I know exactly what's wrong." A disorganized one gives you "something failed somewhere, good luck."

The investment in naming conventions and structure pays back every time you're on the wrong end of a failing build. As always, I'm here to help.

Read more