I've used Nunit and found it useful, but so far haven't applied it to TSQL (mental block?). I'd like to think unit tests have nothing to do with your development strategy, and in many ways don't have anything to do with QA and acceptance testing. Unit tests serve two purposes; verify that the code works as designed, and to provide a safety net if the code needs to be refactored. Refactoring does happen, often months later, and it's no easier in TSQL than anywhere else, it's easy to miss one thing that causes pain, so re-running those tests, while no guarantee of perfection, often quickly identifies the little snafu's that come along.

I've found developers fall into two camps, they love unit tests or they hate them. They are a bit of a leap of faith, and it's been made harder to show them why to love them because many of the tests are too trivial (can I modify trivial with too?). I like the idea of testing for constraints, more than once I've done tuning where someone moved a table without the constraints and all sorts of bad things happened.

I do like Phil's comment on code. I think the reason I haven't spent time on TSQL unit tests is I think they are properly part of the application unit tests, app developer should be testing the various invocation scenarios. The only time TSQL testing seems to make sense if for jobs/processes that are only TSQL.

I'll also speak up in favor of agile techniques. This is another area where there's been a lot of noise and confusion, and it doesn't work in all scenarios without modification (or perhaps at all), but for lots of common business problems it's a very good solution. Unlike diets and get rich quick schemes, agile works when you stick to "a" formula, not necessarily "the" formula.