SQL Server Unit Testing Stored Procedures - generating testdata

Question

I am writing stored procedure unit tests in VS2012 on 2008R2 for a database that keeps a large number of stored procedures, tables and foreign keys.

For each stored procedure test I generate few lines of data in related tables before performing the test.

I have recognized that this practice will make the tests very sensitive to database changes especially to the additions of not null columns or extra keys.

The cascading impact of such changes may result in having to keep a lot of tests in sync. Some tests may even have nothing to do with the particular change but share one or more related tables therefore will fail on preparation.

Also, a rather inconvenient consequence of this is that it is hard to make disctinction between tests failed on testing conditions and those failed on key violation during preparation.

Thinking on a large scale the working hour consequences may be serious.

Anything I found so far on this topic has been way too general.

Now comes the question: does a relevant best practice exist for the question of test data in dev db vs. generating test data within test?

Answer 1

I have been experimenting with writing unit test in VS2012 and found it very limiting and cumbersome. Being triggered by your question I've just did a little reading up on tSQLt which I have been hearing about and it seems to be a more robust testing framework. E.g. it mocks tables, stored procedures etc. This allows for having less dependencies and thus minimizing the cascading impact of database changes.

Even if you still want to write the unit test in VS2012, you could use the mocking functionality. Just makes sure to run your test in a transactions.

Example test from the their website:

CREATE PROCEDURE SalesAppTests.[test SalesReport returns revenue and commission]
AS
BEGIN
-------Assemble
    EXEC tSQLt.FakeFunction 'SalesApp.ComputeCommission', 'SalesAppTests.Fake_ComputeCommission';
    EXEC tSQLt.FakeTable 'SalesApp.Employee';
    EXEC tSQLT.FakeTable 'SalesApp.Sales';

    INSERT INTO SalesApp.Employee (EmployeeId) VALUES (1);
    INSERT INTO SalesApp.Sales (EmployeeId, SaleAmount) VALUES (1, 10.1);
    INSERT INTO SalesApp.Sales (EmployeeId, SaleAmount) VALUES (1, 20.2);

-------Act
    SELECT EmployeeId, RevenueFromSales, Commission
      INTO SalesAppTests.Actual
      FROM SalesApp.SalesReport;

-------Assert
    SELECT TOP(0) *
      INTO SalesAppTests.Expected
      FROM SalesAppTests.Actual;

    INSERT INTO SalesAppTests.Expected (EmployeeId, RevenueFromSales, Commission) 
      VALUES (1, 30.3, 1234.5678);

    EXEC tSQLt.AssertEqualsTable 'SalesAppTests.Expected', 'SalesAppTests.Actual';
END;
GO