Unit Testing TSQL

Question

Is there anybody out there writing unit tests for their TSQL stored procedures, triggers, functions ... etc.

I've recently started making database and restores and installs part of our automated Cruise Control build process. Now I'm thinking about taking it to the next level where we do the install, then run through a list of stored procedure tests etc.

I was going to just roll my own using MsBuild Extensions to invoke the tests. However I'm aware of http://www.tsqltest.org/ and http://tsqlunit.sourceforge.net/. I'm also aware that TFS has sql testing.

I just wanted to see what people in the real world are doing and if they have any suggestions.

Thanks

Answer 1

I have used the database testing that is built into Visual Studio 2008 Database Edition on a project here. It works well, but feels more like a third party bolt-on to Visual Studio than a native component. Some of the pains I felt with it are:

• Because SQL code lives in the res files and a single code file can include multiple tests, it is not as easy to search for tests based on table/column names.
• Because multiple tests live in the same code files, you have some annoying variable name collisions (eg, if you have two tests in a single code file, all of the assertions for those tests have to have unique names; That means your assertion names will probably look like "testname_assertionname", which really shouldn't be necessary).
• Refactoring your tests is not easy - for example, if you want to move a test from one code file to another, the easiest way is to create the test from scratch in the new file because there are bits and pieces of the test scattered about the res file and the code file.

All of that said, as I started with - It does work well. Unfortunately, we have not added these tests to our continuous integration server yet, so I can't comment on how easy it is to automate the running of these tests. We are using TFS for CI, and I am assuming that automation of the tests would work very similar to automation of standard unit tests; In other words, it seems like there should be an MSTest command line that would run the tests.

Of course, this is only an option if you are licensed to run Visual Studio 2008 DB Edition (which I understand is now included in the VS 2008 Pro license).

Answer 2

Stored procedures. I generally include test queries in comments in the SP header, and record correct results and query times. This still leaves it as a manual exercise, however.)

Functions. Again, put SQL statements in the header with the same info.

Triggers. I avoid them for a number of reasons, one of them being that they are so hard to test and debug for so little benefit compared to putting the same logic in another tier. It's like asking how to test for Referential Integrity.

This is still a manual process, however. But since I think one should intentionally design SQL artifacts to be totally uncoupled (e.g. no SPs calling SPs, same with functions, and another strike against triggers IMHO) it's relatively less complex.

Answer 3

The critical parts:

• Make it automated and integrated with your build/test (so you have a green or red from your build)
• Make it easy to add a new test
• Keep your tests up-to-date

Advanced:

• test failure conditions in your code
• make sure your tests clean up after themselves (TSqlTest's example scripts use @beforeCount and @afterCount variables to validate the clean-up)

Answer 4

I've done this in java, using dbunit.

Basically, anything you do in the database either: returns a result set or alters the state of the database.

The state of the database can be described as all the values in all the rows in all the table in all the schemas of a database; the state of any subset is the state of all the data affect by some test.

So, start with a database filled with enough test data that you can perform you tests, call this the baseline. Extract a snapshot, with dbunit or the tool of your choice.

Given that your database is at baseline, any result set is deterministic (as long as your sp is deterministic, less so, if it does a "select random();").

Get the baseline result set of all your SPs, save those as snapshots with dbunit or whatever tool you're using.

To test operations that don't change state, just test that the result set you get is the one you initially got. To test operations that change the database, test that baseline + operation = expected change. After each test that potentially chnages the db, restoe it to baseline.

Basically, the ability to restore to a baseline makes the testing possible.

Answer 5

Have you tried using the red-gate.com API?

They have a bunch of products for comparing things in SQL Server and the API allows virtually the same functionality programmatically.

http://help.red-gate.com/help/SQLDataCompareAPIv5/4/en/GettingStartedAPI.html