Over the past five or so years, encouraging progress has been made with regard to testing .NET application code. Unit testing tools such as NUnit and xUnit have moved from the XP fringes into the mainstream, and most IT departments are at least starting to become aware of the importance of thorough unit testing, as well as regression testing, load/performance testing, integration testing, and so on. I wish I could say that the same progress had been made with testing SQL.

Writing SQL is easy, getting SQL right is not. Database code that performs admirably with a few hundred rows of data and a single connection can fail miserably under concurrent access and millions of data rows. Code can behave entirely differently depending on the exact database setting (for example, collation or isolation level) and yet one often finds many such discrepancies between the testing and production environments. Stored procedures, and more especially triggers, are notorious for producing "unexpected outcomes" in the database and yet rarely get thoroughly tested.

Why is this? Some Database Developers with C# skills have adapted established NUnit techniques to test their database code. However, few DBAs or Database Developers have the required .NET skills, making this a hard path to follow. In addition, this approach represents another instance of the "object-relational mismatch", with an object-oriented tool being used to test a relational model.

A few grizzled "old hands" tend to have their own test harnesses and methods, written in T-SQL and lovingly honed over a number of years. However, for us mere mortals, it is encouraging to see the emergence of some dedicated uniting testing tools for T-SQL. One of them, TSQLUnit, adopts the principles of the object-oriented xUnit frameworks, to provide a stored procedure-driven approach to unit testing T-SQL. A stored procedure is called to execute the tests, another to report failure, and still others can be created to implement, in the xUnit vernacular, test suites, test fixtures and so on. My initial trials suggest it's an easy tool to install and use, but I still find that the task of writing anything but simple tests gets rather complex, very quickly.

Another framework, DBFit, takes an entirely different approach. It is based on the FitNesse acceptance testing framework, which is an end user tool and so very far removed from testing database code. However, it has the advantage that the tests are described in a tabular form that is very intuitive to database people. So, in the following simple example of a test definition, the table name (Execute Backup Procedure) indicates the action to be taken, BackupDatabases is the stored procedure to be executed, and the columns below indicate the inputs and expected outcome:

While these tools show great promise they are, for the moment, firmly on the fringes. Which, in your opinion, represents the best way to test SQL? And what will it take to encourage developers (and DBAs) to test their database code with the same rigour that they are starting to apply to application code?

Cheers,

Tony Davis (Guest Editor)