SQL Server Reference Guide

Software development is now a fairly mature discipline. The fundamentals of programming have been developed since the 1950’s, and in the 70’s and 80’s the discipline was codified and taught in Universities around the world. Within this discipline, students learn the three basic parts of a development project:

• Design
• Code
• Test

While this seems simplistic, you would be surprised to find out that many teams do not follow all of the parts of each of these steps. I’ve seen developers just dive into the code without much design, and when the process is over, very little testing. This, of course, is a recipe for trouble.

In this guide I’m focused on the Database Developer and the Database Administrator (DBA), and as I’ve mentioned, many of us come from one of two areas: developers who are given ownership of the database platform, and system administrators who are given ownership of the database platform.

If you come from the developer world, you have probably had experience with creating tests for your software — or perhaps not. Even if you have, you’ll find that testing database code is much different. If you come from the system administrator role, perhaps you have never done much software testing at all. In either case, this tutorial will give you some practical examples and concepts you can use to create tests for your database objects and code.

In previous articles I have explained how to begin the design work with a good definition of the requirements, how to create a database design with an Entity Relationship Diagram (ERD), and how to begin coding by writing comments. I’ve also explained how to write code in Transact-SQL (T-SQL) and how to create database objects. All that is left is to learn how to test your objects and code.

Why Test?

Most developers know that at least basic testing is important for their code. They may have learned this the hard way — by not testing to begin with. All that does is anger the clients when the software fails, and cause the developer extra work to open the code back up and understand the flow all over again to find what went wrong. The act of scrubbing through code to deal with errors is called “debugging," and it’s the most tedious and unhappy part of development.

So there are two main reasons to test your code, and in the case of the database, the objects. The first is the clients. If you’re in software to make money, then you want your clients to like your software and buy more. The more bugs they find, the more unhappy they are, and the more likely they are to go with the “other guy." If you’re in software for a non-profit reason, then you want to help your clients — and bugs don’t help.

The second reason is rather more selfish. When there is a problem in the code, you’re going to have to fix it. That’s extra work, and it’s hard to do. The later in time the bug is found, the more difficult it will be to find.

By testing the code ahead of time, you’ll be the one to find the bug, and your code will be open, and the logic for the issue will be fresh in your mind.

What’s a Unit Test?

Tests are divided into two major camps, although you can break them down further. When you create a series of tests that ensure the entire systems works as it is supposed to, this is normally called “Functional” testing. Tests in this arena include testing to make sure the system still functions as it did before any changes (regression testing), works as expected under a high load of activity (stress testing), deals with the information sent in from the beginning to an expected value at the end (end-to-end testing) and so on.

The other camp is called “Unit” testing. In this type of testing, the various components and parts of the code are tested to ensure that they work properly. In most development shops, the individual developer creates the tests for what they are working on, making sure that the command “turn blue” turns the screen blue and so on. In fact, in modern development shops, you’ll often see the unit tests written before the actual code is written! This is called “Test Driven Development," or TDD.

In the case of the database, you’re checking for specific objects and results, such as testing to make sure the tables exist and they contain the data types they should have. As I mentioned, this is more difficult in T-SQL, since there are no concepts like inheritance or objects.

How do I Write Them?

In higher-level languages such as C#, tests can be written right into the code itself. For T-SQL, however, this is not very easy. While you can write code in a higher level language to test the database objects, many DBAs and Database Developers may not have access to those.

So what you’ll do is write your tests in T-SQL itself. You might actually end up with more code in the tests than in the code itself, but over time you’ll learn what you can code in bulk, and what you’ll have to run by “hand."

To begin, you’ll want to decide on the kind of tests you want to run. The most basic test is a “validity” or “existence” test. This test simply verifies that the objects you expect to be there are, that they are unique (for instance, you haven’t named a view and a table with the same name), and that they are the same type you expect them to be. Here’s an example that tests for database object existence. It takes two parameters: the name of the object, and the type of the object. The types are described in this tutorial:

/* Validity Check */
CREATE PROCEDURE 
usp_TestDBObjectNameAndType 
@ExpectedObjectName varchar(255)
, @ExpectedObjectType varchar(2)
AS
/*
Purpose: Checks name and type of database object using a sys.sysobjects comparison. Expects the name of a database object, and a datatype from the list shown in the xtype column.
Author: Buck Woody
Last Updated: 10/24/2008
Notes:
*/
IF @ExpectedObjectName = 
	(SELECT name
		FROM sys.sysobjects 
		WHERE NAME = @ExpectedObjectName
		AND xtype = @ExpectedObjectType)
	BEGIN	
		PRINT 'Passed'
	END
ELSE 
		PRINT 'Failed';
GO

I have a database called “WAVS” on my system, and a table I created there called “Person," among others. This statement runs that stored procedure to see if the table exists, and that it is a table:

EXECUTE usp_TestDBObjectNameAndType 'Person', 'U'

To run these tests, I enter the line above in an Excel Spreadsheet on my developer workstation. I then have a line for each object I create as I create it, along with its type. When I’m done with the database, I run the tests, and have each developer that takes a copy of my database keep it handy. That way if they every accidentally drop an object, we’ll know about it before it releases. Even this simple test has “saved my bacon” more than once.

You could expand this test to get the actual content of an object, like the structure of a table or the text of a stored procedure, and compare it to your “known” list. When I do this, I use the scripting functions in DMO, and now in PowerShell.

The next type of Unit test is a “known value” test. This test sends an expected value to a Stored Procedure or Function, and checks that the value is accurate when it comes back. In this example, I expect that the name of a Doctor that I send to a Stored Procedure should come back as the same value, type and so on. So I do a comparison operator to make sure:

-- Expected Return
-- First, a sample procedure
CREATE PROCEDURE usp_FindDoctor 
 @DoctorName varchar(255)
, @ReturnValue varchar(255) OUTPUT
AS
/*
Purpose: Finds all doctors in the database.
Author: Buck Woody
Last Updated: 10/24/2008
Notes:
*/
SELECT @ReturnValue = 
(SELECT PersonName 
	FROM Person
	WHERE PersonName = @DoctorName
	AND PersonType = 'Doctor'); 
GO
-- Now a test for that procedure:
DECLARE @DoctorName varchar(255)
DECLARE @ReturnValue varchar(255)
SET @DoctorName = 'Buck Woody'
EXECUTE usp_FindDoctor @DoctorName, @ReturnValue OUTPUT
IF 
@ReturnValue = @DoctorName
	BEGIN
		SELECT @DoctorName AS 'Expected Value', @ReturnValue AS 'Returned Value', 'Passed' AS 'Test Result'
	END
ELSE PRINT 'Failed'

If the Stored Procedure does some processing or alters the data, I would check for that. If the Stored Procedure should not change the data but does, this I would get a “Failed” message printed on the screen.

I also have some of these tests write out to a database. I run all the tests, and then check the database to ensure that the values are all “Passed." When I run the tests again, I can see if I have a regression failure.

You can expand this test framework to do logic checking as well — anything you like can between the IF statement. The point is, you know what the Function or Stored Procedure can do, so when you write the procedure, write the test.

So far we’ve been talking about “positive” testing. That means we put in what we want, and get back whether that passes or fails. But another important kind of testing is “negative case” testing, which means you put in a value that should fail, and a failure actually represents a successful test. Let me explain what I mean with an example.

Many DBAs (including me) write Stored Procedures for data input into the system. One of the things a stored procedure should do is ensure that the correct data type goes into the right field. In one case I had a person get off by one field in the data entry screen, and sure enough, a date got entered as a Doctor’s name. There are a lot of ways to correct this, perhaps by adding a constraint, but I decided that I want to make sure that any stored procedure I wrote failed when the wrong data type was sent. So here’s an example of a single line from my Excel spreadsheet:

/* Boundary Type Check */
DECLARE @DoctorName DATETIME
SET @DoctorName = '07/07/2007'
EXECUTE usp_InsertNewDoctor @DoctorName;
GO

Placing this logic inside the “IF” statements from above is another way to automate this kind of checking. For my purposes, I just wrapped this with a “RAISERROR” statement and entered the failure (or success) in my tracking database.

Finally, I combine a “stress” test with a performance test on my system. To check the timing, I just use this statement at the beginning of any manual tests I run:

/* Performance Check */
SET STATISTICS TIME ON

For automatic tests, I just store the current date and time in a “Start” field and then in an “End” field:

-- Record the time - Can store these in a variable or database
SELECT GETDATE()
-- Code Run
SELECT GETDATE()

Of course, no testing is complete without security testing. Here I check the permissions, and I also ensure that the stored procedures I have are strongly typed to ensure that no injection attacks can happen.

Unit testing is essential for good code and even more important when you’re the primary DBA or Database Developer. No one will check up on you, and you’ll be the one answering the phone when the system fails. With standard programs this can be frustrating, but if you store the wrong data or don’t store it at all, that’s a bigger problem.

InformIT Articles and Sample Chapters

There’s actually a very handy tool that will help you do all this testing — it’s called Visual Studio Team System Edition for Database Professionals, or “Data Dude." You can read more about that in another section of this SQL Server Reference Guide.

Books and eBooks

Test-Driven Development is a great way to think about testing. Fit for Developing Software: Framework for Integrated Tests is one I’ve read on the subject, and the author does a great job with this idea. (Read in Safari Books Online)

Online Resources

There’s an entire profession for software testing, and you can learn more about it here.