Happy T-SQL Tuesday again! Number 125. I can’t believe how long this thing has been running. In case you weren’t aware T-SQL Tuesday is a blog party created by Adam Machanic (blog|twitter) and now maintained by Steve Jones (blog|twitter). Each month someone hosts and comes up with a subject for all of us to blog about. Then anyone who is interested gets to blog on the same subject. This month our host is Hamish Watson (blog|twitter) (Thanks for hosting Hamish!) and he’d like us to talk about Unit Testing with databases.

To be fair, it’s not something I work with very often. Primarily because my job is helping out hundreds of different groups all of whom have their own ways of handling testing, change management, etc. I’d love it if they were consistent but it’s just no the culture I work in. That said, I realize that this is an excuse but I’m still going with it.

That’s my current position though. I have worked at one place that did an amazing job at testing. It is a company that writes software for hospital emergency rooms so you can see that making sure the software (and of course the database) works correctly is more than a little important.

So to start with any time a code change (or new code) was planned specs were written up. They were then passed to the developers, the DBAs and the testing team. Yes, this company had a team devoted to nothing but testing. (I should also point out that this was 15 years ago.) The developers wrote their code, the DBAs worked on any database changes required (working with the developers of course) and the testing team wrote tests for both the application and the database.

As changes were completed they were checked in to a source control program. This included the scripts necessary to build the database from scratch. The tables, stored procedures, even the lookup data were all checked in. Oh, there was also a bunch of test data developed by a group of nurses and the testing team working together so that the data was as close to real as possible. Then every morning at 6am an automated process pulled the latest set of code and built a copy of the application and the database (including the lookup and test data). If anything failed an email went out to the company with the name of the piece of code that caused the failure, and who checked it in. And that poor person got some ribbing about the whole thing. It was all in good fun because we all knew that might be us next. We were all super careful (no one wanted to see their name in one of those emails) but hey, mistakes happen.

Once the build was completed all of the unit tests, both those for the application and those for the database were automatically run against the new build. Again any failures were noted and corrected as quickly as possible.

And last but not least every quarter the entire company would shut down for several days (except for a skeleton crew in the call center) and we would bash the application. Brownie points were earned for bugs found and documented. The testing was a blast too, the nurses on staff would give us specific areas to work on and tests to run but once that was done our goal was to hit the app (in our assigned area) as hard and fast as we could and try out anything we thought might possibly come up with an error. This included edge testing, volume testing, and load testing (all of us hitting the same things at once).

And all of this worked because we had a culture of testing. Testing was acknowledged as vital. Mistakes and bugs were expected and not taken personally. If you were the source you fixed and moved on. Every piece of code had a test, and it was never assumed that that was sufficient. And finally we all took responsibility for anything we could. If I could fix something I did, if I couldn’t my co-worker could and did. Nothing was personal and the goal was to produce the best product possible. And the only way we could do that was, you guessed it. Testing.