Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

What is the Best Way to Test SQL?

By Tony Davis,

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:

Execute Backup Procedure BackupDatabases
Backup > 24 hrs old TempDB Perform Backup?
Yes No Yes
No No No
Yes Yes No

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?


Tony Davis (Guest Editor)

Total article views: 514 | Views in the last 30 days: 1
Related Articles

Execute a Stored procedure from different databases

Execute a Stored procedure from different databases with context of executing database


Execute SSIS package stored in Database - From Stored Procedure

Execute SSIS package stored in Database - From Stored Procedure


Execute the Database Mail Stored procedures in scheduler

Execute the Database Mail Stored procedures in scheduler


Rights to execute stored procedure

Rights to execute stored procedure


Stored Procedure & 'other' database

How to have a Stored Procedure work on another database


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones