THE SQL Server Blog Spot on the Web

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

Alexander Kuznetsov

Benefit from Unit Testing T-SQL: Reuse Unit Tests as Documentation

Unit tests provide a very accurate snapshot of what we were thinking about (and what we were missing) when we were writing them. In fact, they are working examples of how we think our code should be used. More to the point, unit tests must be up-to-date, otherwise they would not pass.
Fro example, if the signature of a method changes, or we add more use cases, our tests reflect that. As such, unit tests are good a source of current information about our code.

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

How to Benefit from Unit Testing T-SQL: Exposing Failure in a User-Friendly Way

 

Reusing unit tests as documentation

Let us have another look at the test from one of previous posts, How to Benefit from Unit Testing T-SQL: Reusing Manual Tests as Parts of Unit Tests:

-- no messages from jb12345, should return nothing
EXEC dbo.SelectMessagesBySenderNameAndTimeRange
    
@SenderName = 'jb12345';
    
-- must return all messages from jbrown
EXEC dbo.SelectMessagesBySenderNameAndTimeRange
    
@SenderName = 'jb12345';
    
-- must return all messages from jbrown sent no later than 20101004
EXEC dbo.SelectMessagesBySenderNameAndTimeRange
    
@SenderName = 'jb12345',
    
@DateTo = '20101004';
    
-- must return all messages from jbrown sent no earlier than 20101001
EXEC dbo.SelectMessagesBySenderNameAndTimeRange
    
@SenderName = 'jb12345',
    
@DateFrom = '20101001';
    
-- must return all messages from jbrown sent no earlier than 20101001
    -- and no later than 20101004
EXEC dbo.SelectMessagesBySenderNameAndTimeRange
    
@SenderName = 'jbrown',
    
@DateFrom = '20101001',
    
@DateTo = '20101004';

This script demonstrates how we think the code should be used at the time when we are develoiping or changing it. If we misunderstand some requirements, that will be reflected in our tests. If we have forgot about some use cases during development, surely they will be missing from oiur tests as well. Should we need to change the signature of our funstion of stored procedure, we must change our tests accordingly.

Also providing working examples is a very clear and unambiguous way of documenting code.

Documenting assumptions as unit tests

As we develop code, we make assumptions. As Jeff Atwood puts it in The Pragmatic Programmer Quick Reference Guide, "Test assumptions as well as code".  We can explicitly write unit tests which verify that the assumptions used in our development are correct.
I have written a few examples:

Optimizing a query, then documenting assumptions in a unit test.

Yet another example of defensive query optimization

Defensive database programming: SET vs. SELECT.

The next post in this series is: Benefit from Unit Testing T-SQL: Speed up Your Test Harness

 

Published Thursday, October 14, 2010 1:39 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Tim Januario said:

Hello Alexander.  We met at the PASS convention after the database testing block.  I wanted to get in touch with you and discuss some of the was we do database testing.  As I said when I met you, it doesn't seem like a lot of people do it which makes it hard to determine the best practices.  I look forward to speaking with you again.

Tim Januario (tjanuario%connectionsacademy.com)

November 9, 2010 6:18 PM

Leave a Comment

(required) 
(required) 
Submit

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 sqlblog.com, 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 simple-talk.com and devx.com. Currently he works at DRW Trading Group in Chicago, where he leads a team of developers, practicing agile development, defensive programming, TDD, and database unit testing.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement