Testing tools for SQL development

Posted on August 22, 2012


I’ve recently put together an article on using SQL Test/tSQLt, which you can find here.

However, I wanted to briefly talk about the alternatives as I see them.

Before any commercial testing tools were around we could just use a combination of manual scripts that called procedures and used temporary tables to hold data. It wasn’t that intuitive and there was little reason to put the effort in as automating those tests wasn’t easy either.

TSQLUnit is a framework that appears to be no longer supported but went some way to realising the dream. It worked as simple stored procedures that you could run, it is kind of forerunner to tSQLt in my opinion as tSQLt offers a much cleaner approach.

Microsoft has had tools for SQL testing that they introduced as the Database Edition of Visual Studio 2008. Anyone that’s used this will know it didn’t go far enough, it made it easy to create tests for testing the contract but everything else was complex and hugely lacking information. Microsoft’s choice in making it only available to users of this edition when most SQL developers used Management Studio also didn’t help it’s introduction, usage or the product’s evolution.

It’s also available in Visual Studio 2010 but only in Premium or Ultimate Editions (although you can run tests in the Professional Edition). I believe this is a big mistake, everyone needs to be able to write tests – it shouldn’t be a premium feature.

I don’t think database tests are included in Visual Studio 2012 at all, although you can open 2010 projects using the new SQL Server Data Tools.

So, there was no common way to implement TDD, no structured approach and importantly for everyone – very little information or articles demonstrating real examples.

Perhaps there’s just not enough people doing TDD in SQL? I hope that by doing a series of articles that I can help spread the word of TDD for database professionals or anyone that writes SQL as part of their role.