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 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

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