What is database unit testing?

Unit Testing, while a well understood technique in the application development world, is not nearly as prevalent in the data community. Therefore, before jumping into how exactly you would utilize this technique and our toolset around it, you must understand the overall fundamentals of the unit testing methodology. And that's exactly what I'm about to help you do :)

Unit testing provides a structured and automated way for testing individual components of a system. Unit tests are most often authored by the developer of the component under test. As a methodology, it has many advantages over manual ad hoc testing and debugging. By coding test methods, the developer can create a battery of tests that can be run during development to ensure features work as expected. Since unit tests focus specifically on an individual method under test, it is much easier to determine the source of a failure for a failing unit test. Such a battery of tests is very useful for regression testing, since as new features are implemented, existing tests can be re-run to ensure existing functionality has not been broken. Unit tests, in addition, serve as documentation for users of the methods under test. Developers can quickly review unit tests to determine how exactly they are expected to consume particular components.

How does this all relate to database development? The direct analog of application unit tests in the database world are tests of a database’s programmability objects. These include, for example, a database’s stored procedures, functions, and triggers.

What might a database unit test for a stored procedure look like? Let’s say you are attempting to test the CustOrderHist stored procedure in the Northwind database. The stored procedure should give you back the order history for a given customer ID. To test this, you can imagine writing a SQL script that executed the stored procedure and checked whether the expected number of rows were returned. Such a script might look like the following:

DECLARE @CustomerId nchar(5)

SELECT @CustomerId = 'EASTC'

EXEC dbo.CustOrderHist @CustomerId

IF (@@ROWCOUNT <> 19)

RAISERROR('Actual Rowcount not equal to expected 19',1,1)

So what you are looking at above is a very simple unit test for a stored procedure.

Unit testing is not, however, limited to testing the database’s programmability objects. There are many other sorts of database unit tests that one may wish to write. I'll save the specifics about the different kinds of tests you may want to create in another post.

Sachin Rekhi