Mocking Tables with FakeTable: When and Why

One of the most common problems with database unit testing is test isolation. When your stored procedure reads from a real table, your test depends on the state of that table. tSQLt's FakeTable solves this by replacing a real table with an empty, schema-identical copy for the duration of the test.

Why FakeTable Matters

Without isolation, tests become brittle. A test that passes today might fail tomorrow if someone inserts a row that changes the result. FakeTable ensures that each test starts with a clean, predictable state regardless of what is in the actual database.

Basic Usage

Call tSQLt.FakeTable before your test's arrange step. It takes the fully qualified table name and optionally lets you keep constraints or identity columns. tSQLt wraps the entire test in a transaction and rolls back after, so the real table is never actually replaced permanently.

CREATE OR ALTER PROCEDURE [OrderTests].[test that GetTotalRevenue sums correctly]
AS
BEGIN
  -- Replace the real Orders table with an empty fake
  EXEC tSQLt.FakeTable @TableName = 'dbo.Orders';

  -- Arrange: insert controlled test data
  INSERT INTO dbo.Orders (OrderID, Amount) VALUES (1, 100.00);
  INSERT INTO dbo.Orders (OrderID, Amount) VALUES (2, 250.00);

  -- Act
  DECLARE @result DECIMAL(10,2);
  EXEC @result = dbo.GetTotalRevenue;

  -- Assert
  EXEC tSQLt.AssertEquals @Expected = 350.00, @Actual = @result;
END;

Keeping Constraints

By default, FakeTable strips all constraints from the fake table. This is intentional — it simplifies test data setup. But sometimes you want to verify that your code respects a constraint. Use the @Identity and @ComputedColumns parameters to preserve specific behavior.

-- Keep identity column behavior
EXEC tSQLt.FakeTable
  @TableName = 'dbo.Orders',
  @Identity = 1;

When NOT to Use FakeTable

• When you are testing the constraint behavior itself (use the real table or a table with constraints preserved)
• When testing triggers (FakeTable removes them by default)
• When the procedure under test joins many tables and you want integration-level coverage

SpyProcedure: The Companion Tool

FakeTable handles table dependencies. SpyProcedure handles procedure-call dependencies. If the code under test calls another stored procedure, SpyProcedure replaces that procedure with a stub that records how it was called. This is the database equivalent of mocking a function.