tSQLt AssertEqualsTable: The Right Way to Test Complex Query Results

Shannon Lowder

Shannon Lowder

— 2 min read

AssertEquals works great for scalar values — a count, a status flag, a calculated amount. But a lot of stored procedure logic produces result sets: multiple rows, multiple columns. Testing that a procedure returned the right set of rows isn't something you can do with a single scalar assertion.

That's what AssertEqualsTable is for.

How It Works

AssertEqualsTable compares two tables row by row and column by column. If they match exactly, the test passes. If they don't, tSQLt shows you a diff: which rows were in the actual result but not the expected, and which were expected but missing from the actual.

EXEC tSQLt.AssertEqualsTable
    @Expected = '#ExpectedResults',
    @Actual   = '#ActualResults',
    @Message  = 'optional failure context';

Both tables need the same schema — same column names, same types. The comparison is unordered by default, which is usually what you want: you're asserting the set of rows returned, not their physical order.

A Data Transformation Example

Say you have a procedure that summarizes order totals by customer for a given date range:

CREATE PROCEDURE dbo.GetCustomerOrderSummary
    @StartDate DATE,
    @EndDate   DATE
AS
BEGIN
    SELECT
        o.CustomerID,
        COUNT(*)           AS OrderCount,
        SUM(o.TotalAmount) AS TotalSpent
    FROM dbo.Orders o
    WHERE o.OrderDate BETWEEN @StartDate AND @EndDate
    GROUP BY o.CustomerID;
END

Complete test with FakeTable and AssertEqualsTable:

CREATE PROCEDURE OrderTests.[test GetCustomerOrderSummary aggregates correctly]
AS
BEGIN
    -- Arrange
    EXEC tSQLt.FakeTable 'dbo.Orders';
    INSERT INTO dbo.Orders (CustomerID, OrderDate, TotalAmount) VALUES
        (101, '2013-05-01', 100.00),
        (101, '2013-05-15', 200.00),
        (102, '2013-05-10', 150.00),
        (101, '2013-04-30', 999.00);  -- outside date range, should be excluded

    -- Act
    CREATE TABLE #Actual (CustomerID INT, OrderCount INT, TotalSpent DECIMAL(10,2));
    INSERT INTO #Actual
    EXEC dbo.GetCustomerOrderSummary
        @StartDate = '2013-05-01',
        @EndDate   = '2013-05-31';

    -- Assert
    CREATE TABLE #Expected (CustomerID INT, OrderCount INT, TotalSpent DECIMAL(10,2));
    INSERT INTO #Expected VALUES
        (101, 2, 300.00),
        (102, 1, 150.00);

    EXEC tSQLt.AssertEqualsTable '#Expected', '#Actual';
END;

If the date filter is off-by-one and the April order leaks in, the failure output shows exactly which rows disagree and on which columns.

The Failure Output Is the Real Value

When AssertEqualsTable fails, tSQLt produces a diff like this:

Unexpected/missing resultset rows!
|_m_|CustomerID|OrderCount|TotalSpent|
+---+----------+----------+----------+
|<  |101       |2         |300.00    |   (expected, not found)
|>  |101       |3         |300.00    |   (found, not expected)

OrderCount is off by one. You know exactly which row disagrees and on which column. Compare this to an assertion that says "expected 2, got 3" with no context — that's the difference between a test that helps you fix the bug and one that just tells you something is broken.

Testing Column Subsets

When your procedure returns 15 columns and you're only verifying 3 in this particular test, don't force 15 columns into your expected table. Create both temp tables with only the columns you're asserting:

CREATE TABLE #Actual   (CustomerID INT, TotalSpent DECIMAL(10,2));
CREATE TABLE #Expected (CustomerID INT, TotalSpent DECIMAL(10,2));
-- Insert only what's relevant to this test scenario

Focused assertions make failures readable. A test that fails on one specific column is far easier to diagnose than one that fails because any of 15 columns disagreed.

Pair AssertEqualsTable with FakeTable and SpyProcedure and you have a complete toolkit for testing SQL Server logic. Next up: getting these tests running automatically in a build pipeline. As always, I'm here to help.

Read more