• It depends. I find that this is a lot easier to discuss on specific examples and I’d like to analyze a couple.

    Let’s take the code that you present in your article. There you have a couple of functions “NearestBusinessDayOnOrBefore” and “BusinessDaysBetweenEndsOfDays”. I had plenty of cases like that in production code where we had to validate UDFs. In that case you should calculate the results yourself and T.S.T. could be an ideal tool to use.

    If you are talking about a test scenario where you expect a stored procedure to return hundreds or thousands of rows, then you are most likely talk about an integration test and not a unit test. Let’s say that you have a stored procedure that will return a dataset where every row contains information about the cost accrued per day in the context of some financial dealings. Maybe the issue is complicated by the fact that there are a lot of parameters like: discounts that fluctuate with time, service interruptions, variable level of service provided, holidays, client status, legal aspects and so on. If you will feed to the system scenarios that cover a long period of time and include variations of a lot of these parameters you may get a result that will consist of hundreds and thousands of rows where the results are impossible to calculate manually. In this case TST can still be useful especially if you store the baselines in some tables or if the baseline can be calculated by an alternate set of procedures. However, depending of the test infrastructure and the tools you have it is possible you will want to use something different. Like I said, this type of test is not a unit test – it is an integration test. Traditionally there is a lot of confusion between the two and in my experience a lot of people build integration tests and call them unit tests. Ignoring for a moment the terminology issue, such a way of doing things is a valid one and automating it has value but also has certain implication (most of them around maintenance) that have to be understood. I’ll get back to this in a minute. A unit test will be different. You’ll feed to the system a set of scenarios where only one parameter varies and where the result consists of a very small data set typically 1-10 rows. It is a little bit more to be said about how the code and the unit tests can be written in a way that will assure code coverage in scenarios where multiple parameter vary simultaneously. These types of tests will be appropriate for TST and the expected results will be calculated manually.

    Getting back to the case where you do an integration test- that can have great advantages but also has certain downsides.

    Advantages:

    -You can cover a large number of cases that include complicated combinations of the scenarios parameters. Creating and freezing a baseline can be done very productively in this way.

    Downsides:

    -This approach suffers from a test matrix explosion. I have seen cases where this was a significant issue. The result was that not all test cases were covered. Also when calculating the expected results there is a real risk to accept erogenous results. Calculating expected results based on some alternate automation is not trivial. Unit tests will not suffer from the matrix explosion issue.

    -When a calculation rule changes it can be sometimes difficult to recalculate the expected result. Unit tests are great in this case if that were written correctly in the sense that they have a minimum of overlapping.

    I think that both integration tests and unit tests have value but it is important to understand the difference ant their cost implication especially long term related to maintenance in face of changing requirements.

    In conclusion I think that T.S.T can give you a very fast start – asks only for SQL expertise and has no other dependencies. It is also very easy to install and very easy to learn for someone who knows SQL. For large systems it may make sense to be combined or even replaced with heavier and more feature rich frameworks.