A Simple Unit Test Script for SQL Server

As we develop applications, it often makes sense to put some functions in a SQL CLR assembly.  As these assemblies are deployed through test, beta and production environments we need an easy way to ensure the assemblies have been updated correctly and are functioning as designed.  Many things can go wrong—incorrect permissions, deploying the wrong version of an assembly, even assumptions we made in the business logic.  I’m a big fan of RedGate’s SQL Test plugin—it’s an attractive SSMS plugin which organizes and runs tests via an easy to read panel—and the tSQLt (a unit testing framework for SQL Serve) which SQL Test is based on.  My next post will show recreate the testing strategy shown here using SQL Test.

One downside to tSQt is that you have to install a number of stored procedures and functions into your database.  Depending on your choice of database, permissions in your database and willingness to add additional objects to your database which aren’t drectly related to the data, using tSQLt may not be an option.  Although we’re testing a user defined function, we could also test stored procedures and SQLCLR assemblies.  This technique is also not limited to SQL Server—I use this in both SQL Server and VistaDB.  As you build out your application, you may want to consider adding diagnostic pages (or screens) where you can execute these testing procedures should your application start throwing errors.

The function we’ll be testing will be simple—we’ll pass in an nvarchar, and return the same nvarchar with the word “wombat” added to the end.

Start by creating a custom function:

CREATE FUNCTION [AddWombat]
( @phrase NVarChar(50)
)
RETURNS NVarChar
AS
BEGIN
return @phrase + ' wombat'
END

Now it’s time for the test harness.  I’m using VistaDB, creating a stored procedure and using table variables, syntax supported by more recent versions of SQL Server.  You may need to optimize for the database you’re using.  The first table holds our test cases—the test name, the value we want to pass into our function, and the expected result.  We then loop through all the tests, calling the function we want to test, and updating the result.  Our result comparison is very simple, similar to an Assert.AreEqual.  With a little more work, additional test types could be added, and the test indicated in the test case.

create procedure TestAddWombat as
begin

DECLARE @testcases TABLE(TestNumber INT IDENTITY(1,1) PRIMARY KEY,
TestName NVARCHAR(100),
TestCase NVARCHAR(100),
Expected NVARCHAR(100),
Result NVARCHAR(20));

DECLARE @testname VARCHAR(100);
declare @testcase nvarchar(MAX);
DECLARE @expected NVARCHAR(MAX);
DECLARE @result NVARCHAR(max);

DECLARE @testCaseCount INT;
DECLARE @counter INT;

SET @counter = 1;

INSERT INTO @testcases
( TestName ,
TestCase ,
Expected
)

VALUES  ( N'should_return_hello_wombat' , -- TestName - nvarchar(100)
N'Hello, ' , -- TestCase - nvarchar(100)
N'Hello, wombat'  -- Expected - nvarchar(100)
)

INSERT INTO @testcases
( TestName ,
TestCase ,
Expected
)

VALUES  ( N'should_return_null' , -- TestName - nvarchar(100)
NULL , -- TestCase - nvarchar(100)
NULL -- Expected - nvarchar(100)
)

SELECT @testCaseCount = count(*) FROM @testcases

WHILE @counter <= @testCaseCount

BEGIN

SELECT @testname = TestName,
@expected = Expected,
@testcase = TestCase
FROM @testcases
WHERE TestNumber = @counter

SELECT @result = dbo.AddWombat(@testcase)

IF(@expected is NULL)
BEGIN
IF(@result is NULL)
UPDATE @testcases SET Result = 'True' WHERE TestNumber = @counter
ELSE
UPDATE @testcases SET Result = 'False' WHERE TestNumber = @counter
END
ELSE
BEGIN
IF(@expected = @result)
UPDATE @testcases SET Result = 'True' WHERE TestNumber = @counter
ELSE
UPDATE @testcases SET Result = 'False' WHERE TestNumber = @counter
END
SET @counter = @counter + 1;

END

SELECT * FROM @testcases

end

 

 

Running this sproc returns the following results:

 

image

 

From here, we have a very basic test harness which we can easily add additional test cases, and easily extend.  TDD is a best practice in any aspect of application development, but database development is often not treated as application development and is far behind adopting practices common to C# developers.  This script is a simple way to introduce some TDD into database development.

Connecting SQL Source Control to Git in Team Foundation Service

Team Foundation Service is Microsoft’s hosted TFS, and supports both Git and TFS source control.  TFS(ervice) is free for teams up to five, and all repos are private.  If you need a Team Foundation Service account, go to http://tfs.visualstudio.com and log in with your Microsoft ID.  If you’re using Git, you’ll also need to create an alternate set of credentials to connect to the repo.

In SQL Source Control, Git is now a top-level provider, but full Git support hasn’t been implemented yet (Subversion and TFS have much better support in the current version, 3.4 at the time of this post).  For the current version of SQL Source Control, you’ll still need to switch to your favorite Git tool for add/commit/push.  Upcoming features in SQL Source Control for Git include better branching support and support for migrations.  Migrations allow you to alter schema objects without dropping them first—like changing the name of a table without dropping it first—as well as seeding initial data.

1. Create project in TFS

image

 

2. Navigate to Code tab, clone the repo in your favorite Git tool using the URL provided.  TFS only supports HTTPS right now, so you need a set of alternate credentials to use Git.

image

image

 

3. In SSMS, right-click the database you want to put under source control and select “Link database to source control”.

image

 

4. Then, browse to your working folder and select whether each developer will have their own copy of the database, or everyone will work from one central database.

image

 

5. Once the database has been linked, click on the “Commit Changes” tab and choose “Save Changes”.  For a Git repo, this just saves the script files—you still need to do an add, commit and push in your favorite tool.

image

 

6. Once you’ve done an add/commit/push, you can log into TFS again and use all the functionality of diffs, history tracking, etc.  Your other team members can pull from this repo and use SQL Source Control to easily keep their instances up to date.