Do you do it?
I'm talking specifically about the traditional definition of unit testing, as opposed to integration testing (where you would call your stored procedures through the same data access layer used by your application).
I'm aware there are SQL-based unit testing frameworks, does anyone have any experience using these, or perhaps writing C#/VB that directly tests the database (rather than via the DAL)?
Yes, we use and find it extremely useful.
In our case, we are a (very) small team that supports a database that is vital to company business. Our code base is pretty complicated for what it is (C++ on top of SQL) and we don't really have the resources to make any large efforts to fix things. Instead, I've been coming up with ways of improving things with what we have.
I tried the Visual Studio Team test edition (or whatever it's called), but I found it to be a bit too much of an effort for our resources. Instead, we chose TSQLUnit and modified it a bit to serve our purposes. I essentially wrapped its functionality so that we could run the tests as a nightly job and send a summary email if any failures were found.
Next we just created unit tests (stored procedures) when we identified something in the system that we wanted to catch. Most times it's a small issue that ends up rolling into a larger issue if left unchecked. A lot of times it's something that we can't fix with our client software, so it's been a big help.
We don't do it as much as we did, but my group went through a period where we were following test driven development within TSQL. We were using the SQL specific test suite, the name escapes me at the moment, and then we switched over to using the testing available in Visual Studio Team System Database Edition. We're still using that test suite some, but we've found that specific unit tests for stored procs, apart from the code, hasn't provided us much benefit. We get so much more from the unit tests run from the code side because that's where problems occur due to changes in stored procs, more often than not.
Our group has been doing unit testing on backend objects for about 2 years now. We require at least one unit test on each simple database object and on stored procedures the objective is to excercise all lines of the code which may require 3-10 tests depending on complexity. A performance test (response time) is require on all objects.
We are using Visual studio Team System Database Edition.
We think unit testing has important benefits, however, those benefits are not what we expected them to be when we started this practice. Our unit tests rarely find defects in the logic as written. Finding logic errors was why we originally thought we should be doing this.
What we found out was that unit tests help us to produce better code for other reasons. The first is by improving design accuracy. This occurs because our standard is to develop the test logic in advance of writing the module. We confirm how we are going to test by quickly showing some syntax in our stand-up meetings with other team members. By communicating how you are going to test something before you start building it you are forced to clarify your objectives and vision of the handshake with other tiers of the application. Before we followed this practice we inevitably ran into surprises because our team did not have a shared vision of how a backend component was going to supply a web service or the UI. We sometimes had to recode or rejig other tiers to adapt the design. The code today has a cleaner design with fewer data manipulations on other tiers.
The second main benefit is on production maintenance. Because the VS team system allows tests to be easily automated, we run the unit tests all the time and this helps find regression errors which have arisen from changes. A simple change such as changing a column from mandatory to non-mandatory may cause a procedure to fail because it was written with another assumption. The existence of unit tests along with good (and random) data generation allows the team to quickly and inexpensively conduct a round of testing that would be far too expensive to repeat manually for each change. Without unit tests, finding problems would typically wait until the unit acceptance test phase. Today we find them early and the quality we release to UAT is better.
Creating unit tests is more work and slower-up-front but our view is that in the long-run time is saved. Like most disciplines the more you do it the quicker you get at it.
Yes. Invaluable for finding bugs before they hit QA.
A must for making sure changes done do not affect the next tier due to the complexity of some stored procs and legacy systems being converted.
