When we write articles, blog posts, and books, our examples must be simple. On the other hand, when we write automated tests in real life, the most complex modules need automated tests much more than simple ones.
Consider, for example, the following trivial stored procedure:
CREATE PROCEDURE dbo.SelectCountry
SET NOCOUNT ON ;
SELECT CountryCode ,
WHERE CountryCode = @CountryCode ;
RETURN @@ERROR ;
Should we ever need to change this procedure, we can just go ahead and change it. With or without a unit test, changing this procedure would be very easy. More to the point, after changing this procedure we might have to change the corresponding unit tests, if they exist - we shall spend some precious time maintaining that unit test that has never been useful.
If, however, we are dealing with a complex module, unit tests may really help us out big time. Complex modules require more effort to maintain, and benefit more from unit testing, because:
- Complex queries need better documentation, and a well written unit test works very well as such - it demonstrates how to run a module and shows the expected results;
- Complex queries need performance tuning more often, and when tuning we may have to completely rewrite a slow query, improving its performance while keeping its functionality intact;
- Complex modules are more likely to have bugs, and when we fix those bugs, we need to make sure that we haven't introduce other ones;
- Complex modules are more difficult to change if the requirements have changed
Because usually any unit tests involving the database run much slower than unit testing C++ or C# code alone, we typically do not add as many unit tests as we wish - we must keep the overall time the test harness runs low, within acceptable limits. So keeping unit tests for trivial modules may be very counterproductive as our systems grows, as we get more and more unit tests, and as we begin to wait too much until all our tests complete.
However, there is a simple compromise that allows us to at least execute some procedures and do at least some verification of their results - we can use some stored procedures to populate test data. This way we do not explicitly test procedures used in building test data, but we test them implicitly, when we are getting expected results from the unit tests.
This is the second post in the series on unit testing. Here is the previous post.
The next post in this series is: How to Benefit from Unit Testing T-SQL: choosing what not to test