I have been practicing database unit testing for two years, and I am
a big fan of it. My test harness is a great help to me - it lets me
change my modules quickly and with confidence. However, having too many
unit tests is counterproductive - they become a burden and slow you
down rather than help you out.Joel Spolski recently wrote a great
article about the overhead of maintaining your unit tests being "disproportional to the amount of benefit that you get out of them".
That article is a must read!
Unit tests are an overhead.
It takes time to develop unit test, and more time to maintain them.
They also slow down working with source control and searches. For example, every
time your search on a stored procedure name in your solution, every
unit test running in shows up in your search results. Yet we go for
this overhead, because we expect the payoff from better code quality
and easier maintenance to compensate for it. Clearly to break even or
benefit from unit tests we need to keep the overhead within reason.
Unit tests take time to run, sometimes too much time.
To be really useful, your unit test harness needs to run quickly -
otherwise it will slow you down. You will either spend too much time
watching it run, or you will not run it as often as you should - if you
break something, you will not notice it right away. Sometimes this
becomes a serious problem with C# or C++ solutions. Because database
tests usually run much much slower, this may become a serious problem
much sooner. In many cases you simply cannot afford to keep all your
database unit test because of the sheer amount of time they run. Alternatively, database unit testing can be completely abandoned because of this slowness. It can be dismissed as yet another approach that sounds good in theory but does not hold up in the real world. Database unit testing does hold up in real life projects, but only if you use common sense and do it properly.
Unit tests may help you out if you need to change your module.
They can be extremely useful - you can recall what the module is
supposed to do, identify an issue, make a change, and retest in just a
few minutes. This is why we need them most of all. So, the more a unit
test is potentially useful, the more you want to keep it long term. And, using the same logic, if a unit test is very unlikely to ever help you out, there is no reason to keep it.
Complex modules need unit tests most of all. Trivial tests and tests for simple modules can be eliminated.
Complex queries need more maintenance, because it takes more time to
understand and change them, and because they are more likely to need
optimization, This is where unit tests come very handy. On the other
hand, trivial queries such as the following are easier to change and
less likely to need optimization later:
CREATE PROCEDURE Readers.SelectCustomerByID
SET NOCOUNT ON;
SELECT CustomerID, FirstName, LastName
WHERE CustomerID = @CustomerID;
Do you have problems understanding what this procedure does? Do you
expect it will be difficult to change it if needed? Do you think this
procedure needs an explicit unit test?
Do you really need existence tests?
When we write blog posts and articles, our examples must be short
and simple. Running a unit test to determine if your database or your
table exists is a clear and simple example and a great way to learn how
unit tests work - (make sure you have read this article by SQL Server MVP Andy Leonard). However, there is no need to keep such tests in real
production solutions - if a database or a table is dropped, you will
get a clear error message, and you will know what is your problem right away. If a database or a table is dropped, you
will not need an existence unit test to help you out with
troubleshooting, will you?
Make a distinct database call once, and verify everything.
Database call are slow, so you really want to limit the number of
database calls your test harness does. For example, suppose that you
have a complex stored procedure Readers.SelectCustomersByName, and if
you only provide last name, it must return all the customers with that
last name. Because the procedure is complex, you need a unit test for
each distinct case of its usage. Because you want to minimize the
number of database calls, you want to execute the following command
EXEC Readers.SelectCustomersByName @LastName = 'Larsen';
It is very important to verify everything: number of
result sets, number of columns, their names and types, and all the
columns and rows returned by the procedure. The reason is simple: any
change can be breaking, and you need to determine if you broke
something when you do your changes. For more explanations read this
article: "Close those Loopholes - Testing Stored Procedures"
I've used the approach described in that article for two years and it works great. Everything is verified automatically, so I never forget to test anything, and I only need one database call for all my checks, so it runs fast.
Clean up after test driven development.
I like and practice test driven development, however I have the
following reservations regarding it: all-too-often when you think that are done you
have a huge amount of trivial unit tests. That seems to be the whole
idea behind test driven development: every step in your progress is a
unit test. However, there is no need to keep the whole history of your
progress, to keep all the tests that drove your development, for the whole life of your system (each of us humans has had
gills and a tail at some early stage of our development; we don't keep
them forever, we undergo a refactoring). When you are done with your
development, you only need the tests that might help you out in
troubleshooting. Most likely you'll need to consolidate your unit tests
in the way described in the previous chapter. Remember that test driven
development is supposed to consist of the following stages:
- Encounter a non-trivial problem.
- Write a unit test that fails.
- Develop some code so that the test passes.
- Refactor - test driven development does not stop when your tests pass!
I think that this refactoring should include not only your code but
unit tests too. Refactoring is done when your remaining unit tests are
lean, so that the overhead of keeping them is justified by the benefits
of having them.
Let me repeat myself: from where I sit, test-driven development can be a huge help if you are solving a complex problem. However, it can slow you down if the steps you are taking are too easy for you. I guess I should write up some example of test driven development, but it is getting late, so not this time.