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

An Example of Test-Driven Development

By Andy Leonard, (first published: 2009/04/30)

Introduction

In this article I demonstrate principles of test-driven database development (TD3) along with Try/Catch and SqlCmd functionality. The example database is called WeatherData. I actually built the WeatherData database to hold weather data collected by my weather station.

Build the Solution

Did you know you can use SSMS to build a database project? You can. I'm going to use SSMS 2005. To follow along, open SSMS and click File > New > Project:

New Project

When the New Project form displays, select the SQL Server Scripts template in the upper pane:

Script template

In the lower portion of the New Project form, name the project WeatherData:

Solution Explorer

When you click Ok, the database project is created. To view the Solution Explorer, click View > Solution Explorer:

Solution

Solution Explorer, for our empty WeatherData solution, looks like this:

Conenction in Solution Explorer

Write the Test - Part 1: Create the Query File

Test-First Development is a popular variantof Test-Driven Development. You write and execute a test first, execute it, and it fails. This works very well for me, as most of my initial development efforts fail anyway! ;)

The first thing we'll need to do is create the database, so our first test will check for the database.

To create the test, right-click the Queries folder and click New Query:

rename script

When you create the first query, you'll be prompted to connect to a database:

Connect to an instance

Once you connect, the connection is added to the Connections folder:

Current solution

Before we actually write the test, let's give SQLQuery1.sql a more descriptive name. I'd really like to be able to create a subfolder here and name it Tests. Alas, I cannot. So I am constrained to separate types of query files by their name. In this case, I choose to identify test queries by prefixing them with the text: "Test_".

Right-click SQLQuery1.sql and click Rename:

rename file

I chose to name my test "Test_CreateWeatherData.db.sql":

solution

 I like the file naming convention employed by Visual Studio Team System Database Edition. It's [ScriptName].[ObjectType].sql; so I use it here.

Write the Test - Part 2: Write Some Test T-Sql

It's important to stop here and talk some about test theory.

Let's talk first about test results. How will you know the test succeeded or failed? Do you want to return results that can be stored for posterity? If so, how? We'll start with test results that are only available at test time. We are finally ready to actually write some T-Sql!

The first test will be simple:

Use WeatherData

When this test is executed in SSMS, the result is an error message:

Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'WeatherData'. No entry found with that name. Make sure that the name is entered correctly.

The test failed, which is what we wanted, so Success! (Are you confused yet?)

Error Trapping

Another way to approach this test is to trap the error in a way that doesn't cause an error condition to occur. I can hear you asking: "Andy why is it important to not cause an error condition?" I'm glad you asked! Exceptions in the T-Sql will stop the T-Sql execution. The technical term for that is "bad." If the test code doesn't complete, our test results will be inconclusive.

Inconclusive is a valid test result. It's pretty obvious tests can either succeed or fail. Based on this, most assume there are two possible outcomes to a test: success or failure. There are actually four states to two-state logic:

  • On (Success in testing)
  • Off (Failure)
  • Don't Know (Inconclusive)
  • Don't Care (Ignored)

In SQL Server 2005 and beyond, T-Sql provides just the trick for this: Try/Catch. Also included are a couple functions to check for the expected error: Error_Number() and Error_Message(). Let's test this by executing the following T-Sql:

Begin Try
 Select Convert(int,'One')
End Try
Begin Catch
 Print Error_Number()
 Print Error_Message()
End Catch

The results are:

-----------
(0 row(s) affected)
245
Conversion failed when converting the varchar value 'One' to data type int.

The cool part is the query completed successfully - without error:

successful execution

Let's apply this to our database test query:

Begin Try
 Use WeatherData
End Try
Begin Catch
 Print Error_Number()
 Print Error_Message()
End Catch

The results are the same as before:

Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'WeatherData'. No entry found with that name. Make sure that the name is entered correctly.

What happened? Some errors are not trapped by Try/Catch (see Books Online TRY...CATCH topic, Errors Unaffected by a TRY...CATCH Construct section). One category or error unaffected by Try/Catch is "Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution."

Let's TRY Again

It's obvious we need another approach, Try/Catch just won't help here. We can check master.sys.databases for the WeatherData database using the following query:

Use master
Select name
From sys.databases
Where name= 'WeatherData'

This is a better test. We want to test for the existence of the database on this instance of SQL Server, and this does just that. Let's wrap it in a conditional statement that reports the results of the test: 

Use master
If Exists(Select name
          From sys.databases
         Where name = 'WeatherData')
 Print 'Success: WeatherData exists.'
Else
 Print 'Failure: WeatherData does not exist.'

Executing this script reports the result:

Failure: WeatherData does not exist.

Again, the test returns a Failure result, which is exactly what we want. I like this test. Let's keep it.

Next Step: Create the Database

Next, let's write a re-executable T-Sql statement to create the database. I like the test code so much, I'm going to re-use it to make the Create Database statement re-executable:

If Not Exists(Select name
From sys.databases
Where name= 'WeatherData')
 Create Database WeatherData
go

Re-Test 

Finally, we'll re-execute the test after creating the database. Why? Having the test fail is the first step in Test-Driven Database Development. After we meet the condition under test (or attempt to meet it), we need to re-execute the test to determine if we have accomplished the task under test.

When complete, my test-driven T-Sql looks like this: 

Use master
If Exists(Select name
          From sys.databases
          Where name = 'WeatherData')
 Print 'Success: WeatherData exists.'
Else
 Print 'Failure: WeatherData does not exist.'
If Not Exists(Select name
From sys.databases
Where name= 'WeatherData')
 Create Database WeatherData
go 
Use master
If Exists(Select name
          From sys.databases
          Where name = 'WeatherData')
 Print 'Success: WeatherData exists.'
Else
 Print 'Failure: WeatherData does not exist.'

This returns the following results:

Failure: WeatherData does not exist.
Success: WeatherData exists.

Cool. 

Kicking It Up Another Notch

This is good, but we can make it even better. First, save the test query alone as the file Test_CreateWeatherData.db.sql: 

Use master
If Exists(Select name
          From sys.databases
          Where name = 'WeatherData')
 Print 'Success: WeatherData exists.'
Else
 Print 'Failure: WeatherData does not exist.'

Next, create a new query named CreateWeatherData.db.sql in Solution Explorer with the following T-Sql statements:

Use master
go

Print ''
Print 'Create WeatherData database'

If Not Exists(Selectname
              From sys.databases
              Where name = 'WeatherData')
 begin
  Print ' > Creating WeatherData database.'
  Create Database WeatherData
 end
Else
 Print ' > WeatherData database exists.'

Save the CreateWeatherData.db.sql script.

Finally, create one more script in the database project named DeployV1.ver.sql containing the following T-Sql statements:

/*
 Notes:
 1. Turn on SQLCMD mode.
 2. If you receive an error like "Incorrect syntax near ':'",
    see Note 1.
 3. Correct the file paths, replacing the default
    with the full path to your files.
*/

Print ''
Print 'Deploying WeatherData v1.'

Print ''
Print 'Calling Test_CreateWeatherData.db.sql...'
:r "C:\Projects\WeatherData\WeatherData\Test_CreateWeatherData.db.sql"
Print 'Test_CreateWeatherData.db.sql called.'

Print ''
Print 'Calling CreateWeatherData.db.sql...'
:r "C:\Projects\WeatherData\WeatherData\CreateWeatherData.db.sql"
Print 'CreateWeatherData.db.sql called.'

Print ''
Print 'Calling Test_CreateWeatherData.db.sql...'
:r "C:\Projects\WeatherData\WeatherData\Test_CreateWeatherData.db.sql"
Print 'Test_CreateWeatherData.db.sql called.'

Print ''
Print 'WeatherData v1 deployed.'

Before executing this script, drop the WeatherData database (if you've created it). Also, follow the instructions in the comments. Turn on SQLCMD mode in SSMS. When you do this, the SqlCmd scripts will be highlighted in gray as shown:

code

Execute the DeployV1.ver.sql script. If all goes as planned, you should see the following results:

Deploying WeatherData v1.

Calling Test_CreateWeatherData.db.sql...
Failure: WeatherData does not exist.
Test_CreateWeatherData.db.sql called.

Calling CreateWeatherData.db.sql...

Create WeatherData database
> Creating WeatherData database.
CreateWeatherData.db.sql called.

Calling Test_CreateWeatherData.db.sql...
Success: WeatherData exists.
Test_CreateWeatherData.db.sql called.

WeatherData v1 deployed.

Q & A 

I hear you thinking: "Andy, this was a lot more work than simply executing a Create Database statement." I agree. This is an example of Test-Driven Database Development. It's a good demonstration of the amount of additional work required to accomplish TD3.

"So why in the world would we do this?" I am so glad you asked that question!

While this is extra work for the first version of the database, the Unit Test developed here will live on as a Regression Test for the remainder of this database's lifecycle.

"Why not backup and restore the database?" You can do that. It works in many scenarios but not all: remote deployment, for instance. Scripting the database will work wherever restoring a backup will work, but the opposite doesn't always hold.

If you use source or version control, there is some value in comparing scripts. If you don't use source or version control, I predict you will one day.

Make no mistake, there are other ways to accomplish everything I've demonstrated here. There are some fantastic tools out there that help accomplish these tasks. I'll write about them soon.

:{> Andy 

The Series

The articles in this series:

Resources:

WeatherDataProject.zip
Total article views: 27567 | Views in the last 30 days: 10
 
Related Articles
FORUM

database diagram print on one page

database diagram print

FORUM

script to create indexes existing in a database

script to create indexes existing in a database

FORUM

Creating new database with existing MDF(only MDF) file.

Creating new database with existing MDF(only MDF) file.

FORUM

open existing database

can't open existing database

FORUM

creating indexes in existing tables

creating indexes in existing tables

Tags
 
Contribute

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
Editor, SQLServerCentral.com

Already a member? Jump in:

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