Blog Archives

I am speaking at the PASS Summit 2013 !

PASS_2013_SpeakingButton_250x250

I am honored and excited to be selected to speak at the PASS Summit 2013 in Charlotte, NC – Oct 15th through 18th ! I will be talking about “Database Unit testing” with Visual Studio.  This session highlights the importance of Unit Testing in the development life cycle of a Database application. Unit testing a Database application is definitely a lot more challenging than unit testing a VB.NET or C# application . Creating a consistent database test environment not only involves database code, but also the data itself. More often than not, due to the time and effort involved in creating a consistent database test environment, Unit Testing database code is rarely given a though upfront during the development. This usually leads to late discovery of bugs, that are expensive to fix as the development life cycle progresses. Visual Studio, with Database projects and more recently with SQL Server Data tools (SSDT), had made unit testing fairly  easy to implement. During the course of this session, we will touch base with the the concepts of Unit Testing and demonstrate the implementation of Unit tests for a Database project and an SSDT project in VSTS 2010 and VSTS 2012 respectively. If you have already implemented Database Unit test projects in VSTS 2010, we will also go through a demo for upgrading them to SSDT.

I have presented this session at several SQL Saturday events, User group meetings and regional conferences, and I am looking forward to bring to this session to the PASS Summit. I look forward to seeing you all at the Summit in October !

Database Unit Testing Made Easy with Visual Studio Team Systems

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 . Once Created, a Unit Test Project and of the tests will show up in the Solution Explorer View;

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 . Your T-SQL test scripts and the number of test conditions you can use, is only limited by your time and test scope constraints !
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
Two of the most popular ways of organizing a test list is by the level of testing and by functionality;
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. Usually, the end result of this undertaking is a rewarding “Test Results” screen with all indicators in green. Visual Studio also allows you to export and save the test results for future reference;
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:
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: