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: Exposing Failure in a User-Friendly Way

Well-written unit tests should succeed only when the module being tested meets the requirements completely.If the test fails, it can do much better than just indicate failure. If the test has managed to provide a clear and comprehensive report of what is wrong, that can save us a lot of time troubleshooting.

We shall see a few examples soon.

This post continues the series on unit testing, the previous posts are

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

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

How to Benefit from Unit Testing T-SQL. Part One.

How to Benefit from Unit Testing T-SQL: Reusing Manual Tests as Parts of Unit Tests



Verify everything

Surely we can come up with many different scenarios, but in my practice we almost always want to do one and the same thing: capture all the result sets and verify everything. "Everything" means this:Column names and types should be matched precisely in every result set - any change in them may break some code. Order of columns must stay the same.
Similarly, all the values in all result sets should match as well.

Of course, we need to be very specific when we say "all the values in all result sets should match".  For example, if we are not required to order the results, they can be in any order, and our matching must be able to match unordered result sets.
Another example: if we aggregate floating point numbers, we can get slightly different results, and our matching must tolerate minor differences.

Having said all these reservations, my main point remains the same: if the requirements are specific, our testing must be just as specific. For example, checking row count and nothing else makes sense only if the only requirement is to return a specific number of rows. Similarly, checking that the result set is not empty and nothing else makes sense only if the only requirement is to return a not empty result set.

Saving the full description of a result set in a file

Suppose that our test returns the following result set:

Subject                           SentAt
--------------------------------- -----------------------
Free Donuts in Kitchen, Hurry up! 2010-10-07 07:12:00.000
East side printer jammed          2010
-10-07 09:21:00.000
TPS Reports due
in 10 minutes     2010-10-08 11:50:00.000

My testing tool saves column names, types, and all the data returned in this xml file:

<?xml version="1.0" encoding="utf-8"?>
  <object name1="result set" name2="">
    <row type="schema">
      <column name="subject" value="varchar(50)" />
      <column name="sentat" value="datetime" />
    <row type="datarow">
      <column name="subject" value="Free donuts in kitchen area, hurry up!" />
      <column name="sentat" value="10/7/2010 07:12" />
    <row type="datarow">
      <column name="subject" value="East side printer jammed" />
      <column name="sentat" value="10/7/2010 09:21" />
      <column name="subject" value="TPS Reports due in 10 minutes" />
      <column name="sentat" value="10/8/2010 11:50" />


Exposing Failure in a User-Friendly Way

If at some later time our test fails, we want to see the whole picture of what exactly failed, and what returned as expected. There are quite a few utilities which present differences between text files in an easy-to-interpret way.Let us use one of them.

If the test fails, let us output the actual results into an XML file, and use TortioseMerge, which gives a great visual presentation of the differences. For example, the following screenshot is very easy to interpret: we have correct data in the wrong order:


Note that if we were checking only the row count, we would not detect the discrepancies in this case. If we were checking only some value in the first row only, it is just as easy to come up with a scenario when we would fail to detect the discrepancies.
We really want to verify everything.

If we used NUnit assertions to verify all the values individually, we would definitely detect a failure, but we would get less useful information, only one first discrepancy. Similarly, if we were checking all the values individually, output failures, but continue to check, we would be overwhelmed with too many details in the output looking like this, with every value not matching.

Row 0, Column 0,
Expected: Free donuts in kitchen area, hurry up!
Actual: TPS reports due in 10 minutes

Row 1, Column 0,
Expected: East side printer jammed
Actual: Free donuts in kitchen area, hurry up!

Row 2, Column 0,
Expected: TPS reports due in 10 minutes
Actual: East side printer jammed

Based on such too detailed information, it is very difficult to see the forest behind the trees, to detect the pattern in the failures, to quickly understand what went wrong.

As we have seen, if our unit test fails, it is very important to present all the information about the discrepancies in an easy to understand way.

The next post in this series is: Benefit from Unit Testing T-SQL: Reuse Unit Tests as Documentation

Published Friday, October 8, 2010 2:45 PM by Alexander Kuznetsov

Attachment(s): diff3.JPG



Buck Woody said:

I'm doing a database testing overview at PASS this year - I'd love to highlight your scripts. let's hook up on that. I'm at buck dot woody at microsoft dot com.


October 8, 2010 4:50 PM
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