Using TSQLUNIT for SQL unit testing: don't you need to duplicate your SQL code?

Question

Hi, I'm considering writing some unit tests for my Tsql stored procedures, I have two concerns:

1) I will have to write a lot of SQL to create test fixtures (test data prepared in _setup procedures)

2) I will have to "re-write" my query in the test procedure to obtain the results to compare against the results from the stored procedure I'm testing.

Considering that my DB has hundreds of tables and really complex stored procedures... I don't see how this will save me time?? any thoughts? am I missing something? is there any other way to go?

Answer 1

In theory, Unit Testing (in general) means more time up front writing tests, but should make things easier for you later on. For example, the time invested pays dividends later on when you have the ability to spot regression bugs very easily. The wikipedia entry on unit testing has a good overview of the general benefits.

Whether it will be good for you in practice is a hard question to answer - depends on the project.

As for 'having to re-write the query to test the query results', obviously that isn't going to prove anything. I suppose what you need to do is set up test data that will return a predictable result when the query (or whatever) is run, and then test for that specific result. That way you are testing the query against your mental model of it, rather than testing the query against a copy of itself.

But yeah, sounds like that will take a lot of setting up time - I can imagine that preparing a SQL stored procedure test will involve doing a lot more setting-up than your average .Net object test.

Answer 2

The thing I wonder about is, WHY are you considering writing unit tests? Do you have operational issues with the database? Is it hard to implement changes? Is management making your raise dependent on unit tests?

If there's no clear reason, I wouldn't start with unit tests "for fun". When there's a well-oiled change system in place, unit tests add overhead but no value.

There are also serious risks with unit tests:

1. People start seeing unit tests as a "quality guarantee". Just keep hacking till the unit tests give the green light, and then it's good enough for production.
2. Small changes that used to be a "quick fix", will grow bigger because they require (changes to) the unit tests. This way unit tests make you less flexible.
3. Unit tests often check many things that don't matter to anyone using the production system. So unit tests force you to spill resources on stuff only the unit tests care about.

Sorry for the the rant (I've had bad experience with unit tests.)

Answer 3

Hi,

Automated unit-testing often gets left by the wayside as managers push for quick releases rather than increasing project scope and budget to emphasis stability. The fact is, unit-test takes time. In my experience, the benefits far outweigh any drawbacks. In cases where stored procedures are being called by external systems unit-testing has been invaluable in eliminating unforeseen problems and guaranteeing stability prior to integration testing.

Regarding your concerns:

1) If you place any data required to unit test your stored procedure(s) in XML files which can be read prior to running the unit test(s), you can read the data using the standard API routines for reading XML data and potentially re-use the data for multiple tests. Run each test in the context of a transaction which is rolled back at the end of the test to allow the overall environment to be configured once at the beginning of a test run rather than having to perform lots of steps for each individual test. Unit-tests can be bundled with automated nightly build processes to further bullet-proof your code.

There will be some overhead initially, but this will decrease over time as you and your team become more familiar with the unit-test concepts and how to leverage reusability.

2) You shouldn't need to re-write your query to compare the results. A standard scenario might be something like the following:

1. load test data and prepare environment
2. begin transaction
3. run stored procedure using test data
4. compare actual output to expected output using Assert statements
5. if actual and expected output don't match, test fails
6. if actual and expected output match, test passes
7. rollback transaction
   /...
   repeat steps 2 thru 7 for any additional tests
   .../
   
8. Cleanup test environment

Keep in mind, you are testing a specific set of conditions looking for pass/fail so it's Ok to hard code the expected values within your test routines.

Hope this helps,

Bill