THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

How to Benefit from Unit Testing T-SQL: choosing what to test.

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
@CountryCode VARCHAR(3)
SELECT  CountryCode ,
FROM    dbo.Countries
WHERE   CountryCode = @CountryCode ;

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  

Published Tuesday, September 28, 2010 5:04 PM by Alexander Kuznetsov


No Comments
New Comments to this post are disabled

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on and Currently he works as an agile developer.

This Blog


Privacy Statement