Database Unit Test Verification

One of the most important aspects of database unit testing is verification. The application unit testing answer to verification is assertions. Most unit testing frameworks come with a collection of assertions that you can use to verify various conditions, like equality of expected to actual, Boolean value, etc.

In Team Edition for Database Professionals we provide 2 primary mechanisms for doing test verification.

SQL Assertions

The direct analogy to application unit testing assertions are SQL assertions using the RAISERROR command. This command in T-SQL allows you to raise an error with an appropriate message and error level. Thus this command can be used, with conditional logic, to cause the test to fail when the expected result is not met.

This idea is best explained through an example. Let’s write a simple test for the [dbo].[Ten Most Expensive Products] stored procedure in the Northwind database. As you guessed it, this proc returns the test most expensive products in the Northwind products table. Let’s verify in our test that the sproc does in fact return 10 rows. We can do this by executing the sproc and then checking whether the returned row count equals 10. If it does not, we use the RAISERROR command to fail the test.

This is what it would look like:

EXEC [dbo].[Ten Most Expensive Products]

IF (@@ROWCOUNT <> 10)

  RAISERROR('Ten Most Expensive Products did not return 10 rows',1,1)

This example had rather simple conditional logic, but of course you could do any verification logic you wished to in T-SQL to verify that the expected results were in fact met.

Test Conditions

Team Edition for Database Professionals, however, did not stop there in terms of test verification. We realized that there are going to be a set of commonly performed verification tasks and we wanted to offer you a better way of performing these. So we developed the concept of Test Conditions. These are UI-based client-side test conditions that verify the results of your test after the SQL has been executed. You set and configure these inside the database unit test designer.

Designer:

Test Conditions Subpane:

Test Condition Properties:

We include the following set of test conditions:

Row Count

Condition fails if ResultSet does not contain expected row count. This allows you to do light verification ensuring that the number of rows that you expected were returned in the resultset.

Scalar Value

Condition fails if scalar value in ResultSet does not equal expected. This allows deeper verification ensuring that actual values in the returned resultset equal what is expected.

Empty Resultset

Condition fails if the ResultSet is not empty.

Not Empty Resultset

Condition fails if the ResultSet is empty.

Execution Time

Condition fails if the test takes longer than expected execution time to complete. This is the time it takes to execute the SQL against the server using the ADO.NET provider.

Inconclusive

Condition always leads to an inconclusive result. This is the default condition that is added to a test. It is useful to have this by default so that when you run the test, you know that you have not yet completed working on the test. It is a marker for work remaining to be done.

Have other ideas for test conditions we should include? Let me know!

Sachin Rekhi