SQL Server Unit Testing with tSQLt

tSQLt is a free, open-source framework for unit testing in SQL Server. By writing tSQLt test cases, developers can create fake tables and views based on production data, then compare expected versus actual results in testing. Tests are written in T-SQL, so they can be created directly in SQL Server Management Studio.

By using tSQLt, developers can have data created by the test case itself, rather than having to test against a copy of a production database or a separately-maintained test database. All tests are run within transactions, to help reduce cleanup work. tSQLt tests can be grouped together logically in database schema called test classes.

Installing the framework is simple; after downloading tSQLt, the user first needs to enable CLRs on the database:

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

Running the script Example.sql from the tSQLt folder will create a demo database. (To install tSQLt to another database, run ALTER DATABASE with SET TRUSTWORTHY ON.)

The next step is to set up a test class, which will contain multiple test cases. Again, this just creates a new schema:

EXEC tSQLt.NewTestClass 'AcceleratorTests';
GO

Once that's complete, test cases can be created; the following is an example. All test case names must start with 'test' and follow SQL Server stored procedure naming conventions. This example creates a test table, inserts values, and calls the function GetStatusMessage. The tSQLt function AssertEquals checks the expected versus actual results, and the test passes if they match.

CREATE PROCEDURE [AcceleratorTests].[test status message includes the number of particles]
AS
BEGIN
  --Assemble: Fake the Particle table to make sure it is empty and that constraints will not be a problem
  EXEC tSQLt.FakeTable 'Accelerator.Particle';
  --          Put 3 test particles into the table
  INSERT INTO Accelerator.Particle (Id) VALUES (1);
  INSERT INTO Accelerator.Particle (Id) VALUES (2);
  INSERT INTO Accelerator.Particle (Id) VALUES (3);
  --Act: Call the GetStatusMessageFunction
  DECLARE @StatusMessage NVARCHAR(MAX);
  SELECT @StatusMessage = Accelerator.GetStatusMessage();

  --Assert: Make sure the status message is correct
  EXEC tSQLt.AssertEqualsString 'The Accelerator is prepared with 3 particles.', @StatusMessage;
END;

When the test case is run, results are displayed in text (or optionally, output in XML format):

+----------------------+
|Test Execution Summary|
+----------------------+

|No|Test Case Name                                                          |Result 
+--+------------------------------------------------------------------------+-------+
|1|[AcceleratorTests].[test status message includes the number of particles]|Success|
-------------------------------------------------------------------------------
Msg 50000, Level 16, State 10, Line 1
Test Case Summary: 1 test case(s) executed, 1 succeeded, 0 failed, 0 errored.
-------------------------------------------------------------------------------

Once a number of test cases have been created within a test class, they can be run as a batch using EXEC tSQLt.RunAll. For more information on getting started with tSQLt, visit the tSQLt Tutorial.

For those who want to make SQL unit testing part of their continuous build process, tSQLt can be integrated with Cruise Control. As has been reported previously here on InfoQ, there is also a visual interface available for tSQLt called SQL Test. tSQLt is compatible with SQL Server 2005 SP2 and higher.