Using database unit tests in Visual Studio

Stored procedures in SQL Server database can be unit tested using Visual Studio database unit tests. It may seem as simple thing to do at first but when you start writing tests then you will find out it’s more complex task to do then expected. In this posting I will introduce database testing with Visual Studio tools.

NB! In this posting I will use Visual Studio 2010 as I was not able to make database unit tests run on Visual Studio 2012. VS2012 has also these tests available but it seems to me that this support is still raw and waits for stabilization and fixes.

Are they actually unit tests?

I am not very sure that these tests can be called unit tests. They are different than unit tests we are used from applications development. As these tests may also test integrated components in database I would rather call these tests as database tests. Considering these tests to be wider than unit tests is okay because same mechanism allows us write very different tests for databases.

Why database testing?

Databases are often more complex beasts than just some tables, keys and indexes. Often databases contain complex queries, stored procedures and user-defined functions. SQL Server has also support for CLR libraries. All these database objects contain some logic that is usually very important.

Systems that make heavy use of stored procedures are good targets for database tests. Actually there are two ways how to test these databases:

• regular integration tests against some service or set of classes that make data available to system,
• database tests.

Database tests seems better option to me because then we don’t include code from other layers to tests and therefore bugs in other layers cannot affect the results of database tests.

Testing user defined function

Let’s start with simple test that can be considered as unit test. Here is the definition of our function:

CREATE FUNCTION [dbo].[GetImportantValue]  
(
	@n int
)
RETURNS float
AS
BEGIN
	if(ABS(@n)=2)
	begin
		return 0
	end
	return 1.0 / (4 - @n*@n)
END

What can be tested here? Couple of things:

• does the function give expected results with normal values?
• does the function give expected results with special values?

Okay, what’s normal and what’s special value? Normal values are the one that function is expected to accept in all usual cases. Special values in current case are values that cause division by zero. We don’t want to find errors like this in public test or production environments.

Creating database test

We start with creating new test project in Visual Studio and adding database unit test to this project.

After project is created we have there one default unit test and we must remove it as we don’t need it. If we run it we get exception and there is always one failed test in our tests list.

Now let’s add database unit test. Our first test is just call to our function with value 3.

We remove default test condition. Instead of it we will add Scalar value condition as we expect back scalar value. Clicking on condition we can set expected value.

Now the first test is ready.

Running test

Build project and from top menu select Test => Run => All Tests in Solution. Npw all tests in current solution are run and results are shown in test results Windows.

Our test passed and everything this far is okay.

Adding more conditions

We had more than one value to test our function. We can add more tests to our test project but we can also use same test with more than one test condition.

Let’s add tests for these values: –3, –2, 0, 2, 3. As first thing we will modify our test query. As it has to return only five numbers we can add scalar value conditions for these numbers without making test too complex to handle.

Here is one trick with test conditions. We have to change also column index of each scalar condition as our test will return one row with five columns. Running the test we get the following result.

Visual Studio database tests let us actually do much more but this posting is long enough and I will come back to database tests some other time.

Conclusion

Database unit tests are power tool to use when building database that implements some business logic or performs sensitive calculations and we want to be sure that code in database works correctly. There are many ways how database tests can be written and here we started with simple case that gets you going. Database tests have much more to offer and I suggest you to try them out.