Database Unit Testing

Introduction

As database developer we always must ensure that all the database objects are working perfectly and provide output as expected.  But in my professional life I see most of the junior developers are not taken care of that, result unnatural output and not working properly. Here the unit testing of database is very important for each and every person related to database development. I think a junior database developer must understand what I want to say. In this article I am going to discuss about the unit testing of database.

What is unit testing

First we must understand what the unit testing is. If I am going to a bookish definition of unit testing it is a method by which individual units of source code, sets of one or more modules together with associated control data, usage procedures, and operating procedures, are tested to determine if they are fit for use. Here the fit for use is the main key factors. We must question our self that is it really fit for our application or application architecture.

Type of Unit testing

There are two type of unit testing. The Black box unit testing is Inspects from outside and the White box unit testing related to tool under the cover.

Database Unit testing

Database unit testing is used for feature testing of our individual modules like stored procedures, triggers or user defined functions etc. it ensure that subsequent changes of database is not losing my objects functionality.

To write unit tests, we first need to create an isolated database development environment that is separate from our production database and we must take care of the following underlying points.

1.    Create a database project.

2.    Import the schema from your production database to that project.

3.    Deploy the database project to create a database that uses the same schema as the production 

     database.

4.    Populate that database with test data.

Test Condition

It's a pre-defined test conditions to test things. For an example number of rows returned in a result set. For performance point of view the example is the total amount of time that is required to execute.

Each pre-defined test condition has one Assert method which is defined on the base Test Condition class and it is called when evaluating a test condition.

Database Unit Test Case by Visual Studio

Please note that the Database Unit Testing is only supported for VS2010 Premium and Ultimate editions.

You can find a good example from this MSDN link. The blog is written by Atul Verma.

http://blogs.msdn.com/b/atverma/archive/2010/07/28/how-to-unit-test-sql-server-2008-database-using-visual-studio-2010.aspx

 Hope you like it.

Posted by: MR. JOYDEEP DAS