While brushing up on my knowledge of software testing concepts, I came across quite an amusing definition of testing; “To tell somebody that he is wrong is called criticism. To do so officially is called testing” . A programmer usually resents it when a tester finds a defect in his code. We programmers thoroughly unit test our code before handing it off to a tester, because we take pride in developing a bug free application. Some programming languages (C# , VB, ASP.NET) afford themselves to be unit testing easily, because the application is developed within Visual Studio and can readily leverage its unit testing framework.

Visual Studio allows you to create Database projects, and database developers have started embracing it since Visual Studio Team Systems 2008 Database Edition GDR. This offers a robust framework for database developers to identify bugs with their database objects (schemas, stored procedures, functions, etc) by unit testing their database (T-SQL) code, before handing it over to the tester.  Before we jump into the specifics of database unit testing with Visual Studio, the next couple of paragraphs warm us up to the topic by covering a few basic concepts of software testing.

Software testing, undoubtedly plays a important role in the life cycle of most IT Projects. The Goal of any type of software testing is to identify defects to be fixed, so that the product meets requirements and has a deterministic and predictable output. Depending on the testing method employed, testing can be implemented at any time in the development process. Different software development models will focus the test effort at different phases in the development process. Newer development models, such as Agile, often employ test driven development and place an increased portion of the testing in the hands of the developer, before it reaches a formal team of testers.

Software testing methods are traditionally divided into white and black-box testing. These two approaches are used to describe the point of view that a test engineer takes when designing test cases. Unit Testing falls under the category of  white box testing, where the  the tester has access to the internal data structures and algorithms, including the code that implements these. This is in contrast with the black-box testing method, which  treats the software as a “black box”—without any knowledge of internal implementation. A black box tester is usually not a programmer, and  aims to only test the functionality of software according to the applicable requirements. Since the black-box tester has no knowledge on the underlying code, he may find bugs that a programmer misses. However, the same principle can sometimes lead to writing inefficient or incomplete test cases.

Unit Testing is a key component of Test driven development (TDD). Unit Tests are usually written by Developers while they work on the code, to ensure that a specific  of piece of code (Function, Class, Stored procedure, etc) is working as expected. Unit Testing helps to identify defects in the earlier stages of the software development life cycle, where they are cheaper to fix. Unit Testing can prove especially challenging in the world of database development, because of the need for a consistent test environment.

Database Unit Tests are used to establish a baseline state for a database and then to verify any subsequent changes that you make to database objects.  The Unit Testing Framework in Visual Studio (starting with VSTS 2005) helps database developers create, manage and execute Unit Tests for a Database. The Microsoft.VisualStudio.TestTools.UnitTesting namespace supplies classes that provide unit testing support. This namespace contains many attributes that identify test information to the test engine regarding data sources, order of method execution, program management, agent/host information, and deployment data. It also contains custom unit testing exceptions.

You will need the Database Edition GDR of VSTS 2008 or the Ultimate (or Premium) Editions of VSTS 2010 to create, modify and run database unit tests. You can run database unit tests with Professional Edition on VSTS 2010, but cannot create or modify them. Before you can start running database unit tests in VSTS, you must first create a Database Project and then create a test project. The next step is to write sets of Transact-SQL tests that exercise your database objects. Executing these tests in your isolated development environment helps you to verify whether those objects are behaving correctly before you check them in to version control. As changes are made to the database schema, you can use these tests to verify whether the changes have broken existing functionality. A detailed step by step walk through for creating and running database unit tests can be found  here on MSDN .

In a typical database unit test, a Transact-SQL test script runs and returns an instance of the ExecutionResult class. The instance of this class contains a DataSet, the execution time, and the rows affected by the script. All of this information is collected during execution of the script. These results can be evaluated within the Transact-SQL script by using the RAISERROR function, or they can be evaluated by using test conditions. Visual Studio Premium provides a set of the following predefined test conditions for you to use;

• Data Checksum
• Empty Resultset
• Execution Time
• Expected Schema
• Inconclusive
• Not Empty Resultset
• Row Count
• Scalar Value

You can also create negative unit tests to verify expected failures in a stored procedure. A detailed description of the database unit test conditions can he found  here on MSDN .
Visual Studio lets you create test lists to organize unit tests into groups. Test lists are also used to;

• Run multiple unit tests as a group
• Run tests as a part of a Build
• Enforce check-in policy

Visual Studio 11 (currently Beta) comes with a host of unit testing enhancements. Some of the new features include the Unit Test Explorer and Support to Third Party Test Frameworks. More details can be found here on  Peter Provost’s MSDN Blog post .
Visual Studio not only continues to offer a robust framework for implementing database projects, but also makes a compelling case to use it for unit testing database code. It helps us programmers get one step closer to developing a bug free application.
I am presenting a 60 minute session on this topic at the St.Louis Days of Dot Net Conference, Aug 2nd – 4th 2012, where I    will not only cover the basis of unit testing concepts and terminology, but also discuss how unit testing helps ensure and document the quality and accuracy of database deliverables. I will also will run through a demo of creating and running database unit tests using VSTS 2010 . I have spoken on the same topic in the past at the St.Louis SQL Server User Group meeting on June 12th 2012.

References: