Unit Testing in SQL Server (Part 1)

I'm a huge advocate of unit testing your .NET code, but until recently I didn't really know it was possible to test your SQL stored procedures as well. Now I'm a huge advocate of testing those, too.  Hopefully at the end of this tutorial you'll have a solid understanding of how to use the tSQLt framework to write unit tests for your SQL code.

A couple of things to keep in mind as you consider writing unit tests for SQL:

• It's really easy to over-test in SQL; much easier than in .NET because it's tempting to test for impossible scenarios. If scenarios are impossible (because of database constraints, business rules, or some other restriction), strongly consider whether you need to test them before diving in
• Until I wrote unit tests I never considered writing stored procedures in the same way I write .NET code. I would write monolithic stored procedures instead of breaking them into reusable (and testable) pieces. It's a lot easier to test a bunch of small stored procedures than it is to test a single huge stored procedure

First I want to send you to the tSQLt website to get their rundown of this whole thing. You can check it out here.

Now that you've done that, let's get you squared away with the Northwind database. Yes, seriously. Don't look at me like that. Northwind has everything we need and it's still pretty small. It's perfect for this. You can download Northwind from here. That should have given you a backup file that you'll want to restore to a server somewhere. If you have SQLExpress installed that'll work just fine. Before you can run any tests, though, you need to run one command against the Northwind database after you restore it.

DECLARE @Command VARCHAR(MAX)


SELECT @Command = REPLACE(REPLACE@Command, '<<DatabaseName>>', sd.[name]), '<<LoginName>>', sl.[name])
FROM master..sysdatabases sd
INNER JOIN master..syslogins sl
    ON sd.[sid] = sl.[sid]
WHERE sd.[name] = DB_NAME()

Update: I went back and tried to follow these instructions and found that the above script no longer worked.  I'm leaving it here for historic purposes, but below is the script that I ran this time around.

DECLARE @User VARCHAR(50)


SELECT @User = QUOTENAME(sl.[name])
FROM master..sysdatabases sd
INNER JOIN master..syslogins sl
    ON sd.[sid] = sl.[sid]
WHERE sd.[name] = DB_NAME()

All that does is change the database so that you're the owner.

Update: I forgot to include a step here. You need to run the next bit of SQL before you run tSQLt.class.sql or it won't work properly. This is for SQL Server 2008. If you have a different version, search for SP_DBCMPTLEVEL to find out which value you should use.

EXEC SP_DBCMPTLEVEL 'Northwind', 100

OK, now that you have that, you're ready to install tSQLt. Go ahead and get the .zip file from here and extract it somewhere you can find. First run SetClrEnabled.sql on Northwind, then run tSQLt.class.sql.

At this point, tSQLt is installed on Northwind and you should be good to go to write tests. I made a couple of small changes to my instance that I'll share here. We have many users on our environment and we started to see situations where people were renaming tables outside of transactions (we'll get to that, don't worry) and it was causing us some headaches. In order to find the offenders and coach them on what they should do differently I modified a few objects. You can run the below code to make these same changes.

IF OBJECT_ID('tSQLt.Private_RenamedObjectLog') IS NOT NULL
BEGIN
    DROP TABLE tSQLt.Private_RenamedObjectLog
END
GO

CREATE TABLE tSQLt.Private_RenamedObjectLog (
    ID INT IDENTITY(1, 1) CONSTRAINT pk__private_renamedobjectlog_id PRIMARY KEY CLUSTERED
    ,ObjectId INT NOT NULL
    ,OriginalName NVARCHAR (MAX) NOT NULL
    ,[NewName] NVARCHAR (MAX) NULL
    ,RenamedBy VARCHAR(1000) NULL
    ,RenamedOn DATETIME2
)
GO

IF OBJECT_ID('tSQLt.Private_MarkObjectBeforeRename') IS NOT NULL
BEGIN
    DROP PROCEDURE tSQLt.Private_MarkObjectBeforeRename
END
GO

---Build+
CREATE PROCEDURE tSQLt.Private_MarkObjectBeforeRename
(
     @SchemaName NVARCHAR(MAX)
    ,@OriginalName NVARCHAR(MAX)
    ,@NewName NVARCHAR(MAX) = NULL
)
AS
BEGIN

    INSERT INTO tSQLt.Private_RenamedObjectLog (ObjectId, OriginalName, [NewName], RenamedBy, RenamedOn)
    VALUES (OBJECT_ID(@SchemaName +'.' + @OriginalName), @OriginalName, @NewName, SYSTEM_USER, GETDATE())

END
GO

In the next installment, we'll actually check out the framework a little more and write a very basic test.