tSQLt FakeTable: Test Your SQL Logic Without Touching Real Data

Shannon Lowder

Shannon Lowder

— 2 min read

Last time I introduced tSQLt and showed a simple function test. Functions are the easy case — they take inputs and return outputs, no side effects. But most of the logic worth testing in SQL Server lives in stored procedures that read from and write to real tables. That's where FakeTable becomes indispensable.

The problem: your stored procedure queries dbo.Orders. That table has 4 million rows in production and a completely different set of rows in dev. Running a test that depends on what's actually in dbo.Orders isn't a unit test — it's a prayer.

What FakeTable Does

FakeTable replaces a real table with an empty substitute that has the exact same column structure. Within the scope of the test transaction, any query that references dbo.Orders hits the fake table instead. You control exactly what rows are in it. When the test completes and the transaction rolls back, the real dbo.Orders is untouched.

EXEC tSQLt.FakeTable 'dbo.Orders';

After this call, dbo.Orders is empty. Insert exactly the rows your test needs:

INSERT INTO dbo.Orders (OrderID, CustomerID, OrderDate, TotalAmount, Status)
VALUES
    (1, 101, '2012-11-01', 250.00, 'Shipped'),
    (2, 101, '2012-11-05',  75.50, 'Pending'),
    (3, 102, '2012-11-03', 500.00, 'Shipped');

Your test runs against exactly those rows. No production data contaminating your test. No test data contaminating production.

A Real Example

Say you have a procedure that retrieves open orders for a customer:

CREATE PROCEDURE dbo.GetPendingOrders @CustomerID INT
AS
BEGIN
    SELECT OrderID, OrderDate, TotalAmount
    FROM dbo.Orders
    WHERE CustomerID = @CustomerID
      AND Status = 'Pending';
END

Here's the complete test using FakeTable:

CREATE PROCEDURE OrderTests.[test GetPendingOrders returns only pending orders for customer]
AS
BEGIN
    -- Arrange
    EXEC tSQLt.FakeTable 'dbo.Orders';
    INSERT INTO dbo.Orders (OrderID, CustomerID, OrderDate, TotalAmount, Status) VALUES
        (1, 101, '2012-11-01', 250.00, 'Shipped'),  -- wrong status, exclude
        (2, 101, '2012-11-05',  75.50, 'Pending'),  -- include
        (3, 102, '2012-11-03', 500.00, 'Pending');  -- wrong customer, exclude

    -- Act
    CREATE TABLE #Actual (OrderID INT, OrderDate DATE, TotalAmount DECIMAL(10,2));
    INSERT INTO #Actual EXEC dbo.GetPendingOrders @CustomerID = 101;

    -- Assert
    CREATE TABLE #Expected (OrderID INT, OrderDate DATE, TotalAmount DECIMAL(10,2));
    INSERT INTO #Expected VALUES (2, '2012-11-05', 75.50);

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

If GetPendingOrders returns the wrong rows — includes the shipped order, picks up the other customer's data — the test fails and shows you a diff of what was expected vs. what was returned.

Constraints and FakeTable

By default, FakeTable removes constraints from the fake — no foreign keys, no check constraints, no NOT NULL enforcement. This is almost always what you want for unit tests: you're testing your procedure's logic, not the database's integrity enforcement. If you need constraints preserved for a specific test, pass @Defaults = 1:

EXEC tSQLt.FakeTable 'dbo.Orders', @Defaults = 1;

The Underlying Principle

FakeTable is the database equivalent of dependency injection in application code. Instead of letting your procedure reach out and touch the real table, you substitute a controlled fake for the duration of the test. This is the mechanism that makes SQL unit testing repeatable across environments — the test controls its own data universe completely.

Next up: SpyProcedure, which does the same thing for stored procedure calls that FakeTable does for table reads. If you've got questions in the meantime, I'm here to help.

Read more