Andy: Great article: Simple and effective. If people want to delve deeper, that's what the discussion forum is for!

Comparing notes:

Why consider the "test first" technique? 1. I find it helps me focus on one feature at a time, which in turn helps me break down the complexity of a project into many simple parts. So it helps me manage complexity. 2. It keeps testing from becoming a neglected afterthought. I can manage the risks in quality issues up front.

Why write reproducible tests? I feel more confident about making changes and adjustments if I can rerun the tests and get empirical evidence that things aren't broken. If I can efficiently make changes, I'm more agile.

I develop mostly in SQL Server 2000 right now as I'm sure others do, which doesn't have the try/catch feature. However, there are tools in 2000 for raising errors: RAISERROR ('User-defined Error in usp_ThisStoredProcedure.', 16, 1). custom code or a framework can offer reporting of test success and failure: EXEC tsu_failure @FailureMessage.

I've been using the TSQLUnit testing framework: http://tsqlunit.sourceforge.net/. TSQLUnit gives me the test suite logical grouping of tests with a setup and teardown for the group. Test code is written in stored procedures. The framework organizes the stored procedures through simple naming conventions. The framework gives some simple stored procedures to execute to run and manage the tests. Changes are rolled back at the conclusion of the tests.

I find that I use the unit tests mainly to set up and use test data (insert, update and delete test records) and test the functionality of stored procedures (did the stored procedure delete the test recordset as expected?).

I tend to not test all DDL (data definition language) statements such as whether or not certain objects exist except when necessary. For most of my DDL test needs, I've got the RedGate tool SQL Compare which compares every aspect of every object between my development and production. So, if the goal is to make sure nothing unexpected has changed, this tool does it for me in my environment where I don't require remote deployment.

As a developer, I feel more agile when I use test-driven development techniques and I think I produce better quality code. It can be done even in SQL Server 2000.