Search results matching tags 'T-SQL', 'Unit testing', and 'Database Unit Testing'

Benefit from Unit Testing T-SQL: Speed up Maintenance of Unit Tests

Alexander Kuznetsov — Wed, 27 Oct 2010 21:00:00 GMT

Once we have a considerable amount of unit tests, their maintenance begins to take noticeable time. If we need to change a module covered by unit tests, it may take less time to make the change itself than to change the corresponding tests accordingly.

This post continues the series on unit testing, the previous posts are

How to Benefit from Unit Testing T-SQL: choosing what not to test

How to Benefit from Unit Testing T-SQL: choosing what to test

How to Benefit from Unit Testing T-SQL. Part One.

How to Benefit from Unit Testing T-SQL: Reusing Manual Tests as Parts of Unit Tests

How to Benefit from Unit Testing T-SQL: Exposing Failure in a User-Friendly Way

Benefit from Unit Testing T-SQL: Reuse Unit Tests as Documentation

Benefit from Unit Testing T-SQL: Speed up Your Test Harness

We may not notice this effect when we are evaluating a new technology against a small set of tests. However, in real life, and with a project of any reasonable size, we will surely notice how unit tests slow us down. Typically we shall start paying attention to tests maintenance as soon as we need to change a module covered by them, most likely long before we have just one thousand of unit tests.

Let me quote from the blog post The Maintainability of Unit Tests
written by my coworker Jay Fields, who, together with Shane Harvey, Martin Fowler, and Kent Beck, wrote Refactoring: Ruby Edition

Jay says the following: "In my experience, making the interface or interaction change often takes 15-20% of the time, while changing the associated tests take the other 80-85%. When the effort is split that drastically, people begin to ask questions. Should I write Unit Tests? The answer at speakerconf was: Probably, but I'm interested in hearing other options."

As we discussed before in "How to Benefit from Unit Testing T-SQL: Reusing Manual Tests as Parts of Unit Tests", we have chosen to separate expected results from the tests themsleves, and to generate those expected results rather than hardcode them. One of the reasons for this choice was to speed up tests maintenance. Let us see how having expected results decoupled from the tests themselves makes maintenance so very much faster.

Changing tests to accommodate changes in the module being tested

Suppose that we have a stored procedure which returns the following columns: Subject, SentAt, exactly as we discussed in "How to Benefit from Unit Testing T-SQL: Exposing Failure in a User-Friendly Way".
Suppose that we have modified this procedure: we added one more column, Priority, to its result set. With expected results decoupled from the test itself, the test stays the same, and we only need to change expected results. That is very easy: we can just regenerate the expected results, just using the same tool that generated them, and overwrite the files.

Note: Both version control systems I am using right now, Subversion and Git, do not require me to explicitly check out, or unlock, the files being modified, the files with expected results - I can just overwrite them without any effort. Of course, if you are using an old fashioned version control system that requires you to explicitly unlock or check out files before you can modify them, you are in for much more work.

Let us visually review all the changes and confirm that the only change in the expected results is that additional column, like in the following screenshot from TortoiseSVN diff tool.

Once we have confirmed that our procedure still works as before, but returns an additional column, we can just commit the changes into version control and we are all set.

Naturally, as we visually verify the changes in the expected results, we can make mistakes, like in any other task. However, because the process of changing expected results is automated, and because the changes are exposed in a very user friendly way, we are not likely to make those mistakes. Overall, this is a safer and faster way of maintaining unit tests than just fixing them manually.

The next post in this series is Benefit from Unit Testing T-SQL: Speed up Maintenance of Unit Tests Part Two

Benefit from Unit Testing T-SQL: Speed up Your Test Harness

Alexander Kuznetsov — Fri, 15 Oct 2010 19:04:00 GMT

Ideally unit test harness should run very fast. Because all unit testing involving a database is quite slowish, we need to apply some effort to ensure that our tests finish in acceptable time.

This slowness might not be a concern when we try out a cool new technology against a tiny set of objects and tests, but it surely does become an issue when we unit test a real life system of any reasonable size. In this post we shall learn how to increase the speed of our test harness.

This post continues the series on unit testing, the previous posts are

How to Benefit from Unit Testing T-SQL: choosing what not to test

How to Benefit from Unit Testing T-SQL: choosing what to test

How to Benefit from Unit Testing T-SQL. Part One.

How to Benefit from Unit Testing T-SQL: Reusing Manual Tests as Parts of Unit Tests

How to Benefit from Unit Testing T-SQL: Exposing Failure in a User-Friendly Way

Benefit from Unit Testing T-SQL: Reuse Unit Tests as Documentation

Do not explicitly test modifications

Explicitly testing modifications is definitely doable, but it is quite slow.
Besides, we cannot easily run such tests concurrently from several connections, unless we use separate databases. Instead, we should test modfications implicitly - we use them to build up at least some of the test data, and verify that our selects return expected results.

Use one and the same test data for all tests

Adding test data specifically for a group of tests and tearing it down afterwards uses up too much precious time. Instead, we should run all tests off of the same test data. As an additional beefit, that allows us to run tests in parallel against the same test database. Of course, when we change test data to accomodate a unit test, this can break other tests. We shall address that in a later post.

Use C#, not T-SQL, to match actual results against expected.

We can love T-SQL as much as anyone else, but it is simply not the best tool for the job. First of all, it cannot capture more than one result set. Besides, matching actual results against expected in C# is in my experience much faster. One and the same C# module can work against all result sets. On the other hand, should we be matching results in T-SQL, a lot of time would be spent parsing and compiling SQL.

Concentrate on testing only the most important and the most complex modules.

We have discussed that before, in How to Benefit from Unit Testing T-SQL: choosing what to test , but it is definitely worth repeating here.

Next steps.

When we use one and the same test data for all tests, we may need to change it from time to time. These changes may break some tests. Fixing them should be fast and easy. We shall address it in the next post.

Benefit from Unit Testing T-SQL: Reuse Unit Tests as Documentation

Alexander Kuznetsov — Thu, 14 Oct 2010 17:39:00 GMT

Unit tests provide a very accurate snapshot of what we were thinking about (and what we were missing) when we were writing them. In fact, they are working examples of how we think our code should be used. More to the point, unit tests must be up-to-date, otherwise they would not pass.
Fro example, if the signature of a method changes, or we add more use cases, our tests reflect that. As such, unit tests are good a source of current information about our code.

This post continues the series on unit testing, the previous posts are

How to Benefit from Unit Testing T-SQL: choosing what not to test

How to Benefit from Unit Testing T-SQL: choosing what to test

How to Benefit from Unit Testing T-SQL. Part One.

How to Benefit from Unit Testing T-SQL: Reusing Manual Tests as Parts of Unit Tests

How to Benefit from Unit Testing T-SQL: Exposing Failure in a User-Friendly Way

Reusing unit tests as documentation

Let us have another look at the test from one of previous posts, How to Benefit from Unit Testing T-SQL: Reusing Manual Tests as Parts of Unit Tests:

-- no messages from jb12345, should return nothing EXEC dbo.SelectMessagesBySenderNameAndTimeRange @SenderName = 'jb12345'; -- must return all messages from jbrown EXEC dbo.SelectMessagesBySenderNameAndTimeRange @SenderName = 'jb12345'; -- must return all messages from jbrown sent no later than 20101004 EXEC dbo.SelectMessagesBySenderNameAndTimeRange @SenderName = 'jb12345', @DateTo = '20101004'; -- must return all messages from jbrown sent no earlier than 20101001 EXEC dbo.SelectMessagesBySenderNameAndTimeRange @SenderName = 'jb12345', @DateFrom = '20101001'; -- must return all messages from jbrown sent no earlier than 20101001 -- and no later than 20101004 EXEC dbo.SelectMessagesBySenderNameAndTimeRange @SenderName = 'jbrown', @DateFrom = '20101001', @DateTo = '20101004';

This script demonstrates how we think the code should be used at the time when we are develoiping or changing it. If we misunderstand some requirements, that will be reflected in our tests. If we have forgot about some use cases during development, surely they will be missing from oiur tests as well. Should we need to change the signature of our funstion of stored procedure, we must change our tests accordingly.

Also providing working examples is a very clear and unambiguous way of documenting code.

Documenting assumptions as unit tests

As we develop code, we make assumptions. As Jeff Atwood puts it in The Pragmatic Programmer Quick Reference Guide, "Test assumptions as well as code". We can explicitly write unit tests which verify that the assumptions used in our development are correct.
I have written a few examples:

Optimizing a query, then documenting assumptions in a unit test.

Yet another example of defensive query optimization

Defensive database programming: SET vs. SELECT.

The next post in this series is: Benefit from Unit Testing T-SQL: Speed up Your Test Harness

How to Benefit from Unit Testing T-SQL: Exposing Failure in a User-Friendly Way

Alexander Kuznetsov — Fri, 08 Oct 2010 18:45:00 GMT

Well-written unit tests should succeed only when the module being tested meets the requirements completely.If the test fails, it can do much better than just indicate failure. If the test has managed to provide a clear and comprehensive report of what is wrong, that can save us a lot of time troubleshooting.

We shall see a few examples soon.

This post continues the series on unit testing, the previous posts are

How to Benefit from Unit Testing T-SQL: choosing what not to test

How to Benefit from Unit Testing T-SQL: choosing what to test

How to Benefit from Unit Testing T-SQL. Part One.

How to Benefit from Unit Testing T-SQL: Reusing Manual Tests as Parts of Unit Tests

Verify everything

Surely we can come up with many different scenarios, but in my practice we almost always want to do one and the same thing: capture all the result sets and verify everything. "Everything" means this:Column names and types should be matched precisely in every result set - any change in them may break some code. Order of columns must stay the same.
Similarly, all the values in all result sets should match as well.

Of course, we need to be very specific when we say "all the values in all result sets should match". For example, if we are not required to order the results, they can be in any order, and our matching must be able to match unordered result sets.
Another example: if we aggregate floating point numbers, we can get slightly different results, and our matching must tolerate minor differences.

Having said all these reservations, my main point remains the same: if the requirements are specific, our testing must be just as specific. For example, checking row count and nothing else makes sense only if the only requirement is to return a specific number of rows. Similarly, checking that the result set is not empty and nothing else makes sense only if the only requirement is to return a not empty result set.

Saving the full description of a result set in a file

Suppose that our test returns the following result set:

Subject                           SentAt
--------------------------------- -----------------------
Free Donuts in Kitchen, Hurry up! 2010-10-07 07:12:00.000
East side printer jammed          2010-10-07 09:21:00.000
TPS Reports due in 10 minutes     2010-10-08 11:50:00.000

My testing tool saves column names, types, and all the data returned in this xml file:

<?xml version="1.0" encoding="utf-8"?>
<results>
<object name1="result set" name2="">
    <row type="schema">
      <column name="subject" value="varchar(50)" />
      <column name="sentat" value="datetime" />
    </row>
    <row type="datarow">
      <column name="subject" value="Free donuts in kitchen area, hurry up!" />
      <column name="sentat" value="10/7/2010 07:12" />
    </row>
    <row type="datarow">
      <column name="subject" value="East side printer jammed" />
      <column name="sentat" value="10/7/2010 09:21" />
    </row>
      <column name="subject" value="TPS Reports due in 10 minutes" />
      <column name="sentat" value="10/8/2010 11:50" />
</object>
</results>

Exposing Failure in a User-Friendly Way

If at some later time our test fails, we want to see the whole picture of what exactly failed, and what returned as expected. There are quite a few utilities which present differences between text files in an easy-to-interpret way.Let us use one of them.

If the test fails, let us output the actual results into an XML file, and use TortioseMerge, which gives a great visual presentation of the differences. For example, the following screenshot is very easy to interpret: we have correct data in the wrong order:

Note that if we were checking only the row count, we would not detect the discrepancies in this case. If we were checking only some value in the first row only, it is just as easy to come up with a scenario when we would fail to detect the discrepancies.
We really want to verify everything.

If we used NUnit assertions to verify all the values individually, we would definitely detect a failure, but we would get less useful information, only one first discrepancy. Similarly, if we were checking all the values individually, output failures, but continue to check, we would be overwhelmed with too many details in the output looking like this, with every value not matching.

Row 0, Column 0,
Expected: Free donuts in kitchen area, hurry up!
Actual: TPS reports due in 10 minutes

Row 1, Column 0,
Expected: East side printer jammed
Actual: Free donuts in kitchen area, hurry up!

Row 2, Column 0,
Expected: TPS reports due in 10 minutes
Actual: East side printer jammed

Based on such too detailed information, it is very difficult to see the forest behind the trees, to detect the pattern in the failures, to quickly understand what went wrong.

As we have seen, if our unit test fails, it is very important to present all the information about the discrepancies in an easy to understand way.

The next post in this series is: Benefit from Unit Testing T-SQL: Reuse Unit Tests as Documentation

How to Benefit from Unit Testing T-SQL: Reusing Manual Tests as Parts of Unit Tests

Alexander Kuznetsov — Wed, 06 Oct 2010 13:36:00 GMT

In this post we shall see how to incorporate a typical manual test in an automated test harness with minimal effort.

Because our goal is to benefit from unit testing, and not to develop as many features as we can think of, we shall concentrate on the most common use cases. In fact, just one use case is so common that is happens more often all all others combined together, at least in my practice. Naturally this whole "the most common" thing is very subjective, and your perspective may be very different from mine. Anyway, the following example demonstrates what I think is the most common scenario in my practice. Supporting this very common scenario allows me to gain about 80% of possible benefits by implementing about 5% or less of possible features (naturally, my estimates are very subjective).

This post continues the series on unit testing, the previous posts are

How to Benefit from Unit Testing T-SQL: choosing what not to test

How to Benefit from Unit Testing T-SQL: choosing what to test

How to Benefit from Unit Testing T-SQL. Part One.

Developing a stored procedure

So here is my example of the most common scenario in my day-to-day development activity.
Suppose that we need to implement this requirement: "For a given sender name, and a range of DATETIME values, select all the messages sent by the sender between these two times, ordered by the time they were sent. "Between" must mean the way SQL interprets it, with both ends included. One or both ends of the interval can be omitted, in which cases we must return the following:

If DateFrom is omitted, return all the messages from the sender sent no later than DateTo,
If DateTo is omitted, return all the messages from the sender sent no earlier than DateFrom,

If both DateFrom and DateTo are omitted, return all the messages from this sender"

We have already discussed that we prefer to test complex modules, so let us assume that this is a challenging task, and that developing this module fascinates us. In other words, this module is complex enough and eventually we need unit tests for it.

Here is the procedure that implements these requirements:

CREATE PROCEDURE dbo.SelectMessagesBySenderNameAndTimeRange @SenderName VARCHAR(30), @DateFrom DATETIME = NULL, @DateTo DATETIME = NULL AS BEGIN SELECT Subject, Body, SentAt FROM dbo.Messages WHERE SenderName = @SenderName AND SentAt>=COALESCE(@DateFrom, SentAt) AND SentAt<=COALESCE(@DateTo, SentAt) ; END

Note: Maybe this implementation is not performant enough, and at some later time we shall need to speed it up. Right now we are making sure that this module work correctly. Maybe, however, this procedure will perform well enough for the time we need it, so let us not over-engineer it just yet, let us not optimize it before we know we need optimization. For now, let us concentrate on the correctness of this procedure.

Developing manual tests

Even if there is considerable pressure to get things done fast, we absolutely want to test the module as it is being developed, manually or automatically. For thorough testing, we need enough test data to cover all the cases. It does not really matter too much whether we generate test data with the help of some tool, or just type some insert commands manually - there are many possible ways to accomplish this task. Yet it does matter very much that the test data represents all the cases.

Suppose that we have already come up with the procedure and all the needed test data, and that we want to test the procedure. I find it easier and faster to first test my procedure manually, in SSMS, as shown in the following script:

-- no messages from jb12345, should return nothing EXEC dbo.SelectMessagesBySenderNameAndTimeRange @SenderName = 'jb12345'; -- must return all messages from jbrown EXEC dbo.SelectMessagesBySenderNameAndTimeRange @SenderName = 'jb12345'; -- must return all messages from jbrown sent no later than 20101004 EXEC dbo.SelectMessagesBySenderNameAndTimeRange @SenderName = 'jb12345', @DateTo = '20101004'; -- must return all messages from jbrown sent no earlier than 20101001 EXEC dbo.SelectMessagesBySenderNameAndTimeRange @SenderName = 'jb12345', @DateFrom = '20101001'; -- must return all messages from jbrown sent no earlier than 20101001 -- and no later than 20101004 EXEC dbo.SelectMessagesBySenderNameAndTimeRange @SenderName = 'jbrown', @DateFrom = '20101001', @DateTo = '20101004';

Suppose that these test cases cover all the possible situations we know of (maybe I am missing something, but I don't know it at this time), and that when we run this manual test script in SSMS against our test data, we are sure all the results are correct. Now that we have a working procedure, we need to come up with unit tests equivalent to the script above. Of course, we also need to document the module, and to add all the results of our work to version control.

Needless to say, we want this process of coming up with unit tests to be very efficient, because we are going to repeat it many times a day.

From manual tests to automated without writing code

So I have come up with the following simple process: to indicate that my manual test should be incorporated into my test harness, I just save it into a folder named Tests. I have a little utility which executes the manual test and saves all the output from it in a separate XML file, in the same tests folder, with the same name as the sql test script.

When I have my expected results persisted in a file, I can run another utility which executes my test script and matches its results against the XML file with my expected results. Note, however, that I don't have to write a single line of code neither for expected results, nor to match the actual results against expected.

In the past I was writing trivial glue C# code to invoke my T-SQL modules and to match the actual results against expected ones, as we described in Close those Loopholes - Testing Stored Procedures

This worked very well for me, but surely not having to write any code at all, having a tool do everything for us, is more efficient than having to write trivial glue code. So here we are, eliminating fluff, getting rid of unnecessary complications.

So let me summarize the process described above: I will spend as much time as needed,
making absolutely sure that I have enough test data and enough test cases. I will not cut any corners and will not compromise the quality of my code until I know that my code works.

However, when I know that my code works, I will save my manual test in Tests folder, to indicate that this is a test. I will generate my expected results, and save them as a file in the same Tests folder. and I do not need to write a single line of code to invoke my tests. A utility will run this test for me.

Generating expected results is faster and less prone to errors, than doing it manually

The process of reading a test script from a file, executing it against some test database, and matching the results against the expected ones is highly repetitive. As such, it can be fully automated. This – the fact that expected results are generated and not manually developed - may be considered by some professionals as a very serious compromise.

Some, probably many, will not approve the fact that I generate my expected results, some will say that it violates some kind of basic principle of unit testing or TDD. Maybe so. Yet this approach saves me a huge amount of time, and allows me to concentrate on what really matters, what really makes the difference: building a comprehensive set of test cases.

In this case, should we choose to stick to pure theoretical principles, we have nothing to gain and a lot of time and effort to lose. More to the point, coding the expected results manually is a very mundane and time-consuming process. As such, it is very prone to errors. In addition to the time spent manually defining the expected results, a lot of time is usually spent fixing bugs in our test harness. When we generate expected results, we eliminate the source of such bugs and save a lot of time.

Next steps

Note that our test script can and should be also used as documentation, which saves even more time. We shall discuss it a later post.

Another important point here is this: when the test script is stored separately from its expected results, this can simplify maintenance. We shall talk about it more in some later post.

One last note: if at some later time our unit test fails, we need to expose the discrepancies between actual and expected results in a very clear way. When the expected results are stored in a separate file, this is very easy to accomplish. This is described in my next post, How to Benefit from Unit Testing T-SQL: Exposing Failure in a User-Friendly Way

How to Benefit from Unit Testing T-SQL: choosing what not to test

Alexander Kuznetsov — Thu, 30 Sep 2010 20:55:00 GMT

Ideal unit tests are easy to roll out, easy to maintain, run fast, and make a difference: without them making changes and troubleshooting are more difficult.

Some common examples of unit tests do not grade well against this criteria. For instance, writing unit tests to verify that a database or a table exists is surely easy, and is a nice learning excersize. However, such tests do not make troubleshooting any easier: if our code refers a non-existing table or database, the database engine will throw a clear to understand error message. If this error message does not get stored in the error log, we should improve error handling. To verify that the DDL in our version control is in sync with the real thing, we can use tools such as SQL Compare.

Explicitly testing database modifications is a very interesting challenge. We enjoyed solving it, and wrote an article describing the approach: Close These Loopholes - Testing Database Modifications. However, explicitly testing database modifications is rarely feasible: typically testing database modifications runs too slow, and the tests are quite brittle - we usually need to maintain them if the database schema changes. Suppose, for example, that we have added one more column to a table. If our select stored procedure does not use this new column, all existing unit tests invoking it can run as is. Unit tests verifying modifications of this table, however, usually need to change - they need to either ignore this new column, or to include this new column in their expected results.

Usually we just invoke stored procedures with database modifications to build test data, as follows:

EXEC Writers.InsertCustomer @ID=1, @LastName='Kirk', @FirstName='Burns'; EXEC Writers.InsertCustomer @ID=2, @LastName='Jason', @FirstName='Wu'; EXEC Writers.InsertCustomer @ID=3, @LastName='Jane', @FirstName='Larsen'; EXEC Writers.InsertCustomer @ID=4, @LastName='Dawn', @FirstName='Arrow'; EXEC Writers.UpdateCustomer @ID=1, @LastName='Kurt', @FirstName='Burns'; EXEC Writers.DeleteCustomer @ID=4;

The excpected results of such modifications are tested implicitly, when we run selects against this test data nd verify that we get the expected results.

If the data being selected includes identities, rowversion columns, or other columns that are not going to be the same the next time we run test, we can either exclude them from our checks, or make sure that they come out exactly the same every time we run test. Usually it is way simpler to make sure that the data is the same. Note that we cannot do that for rowversion columns - we have no cotrol over their values. Also if we insert multiple rows in one statement, we cannot guarantee which rows get which identities, unless we use IDENTITY_INSERT. The following script demonstrates it:

BEGIN TRY -- we need this because DBCC CHECKIDENT has some gotchas INSERT INTO dbo.Customer DEFAULT VALUES END TRY BEGIN CATCH PRINT '1' END CATCH GO DBCC CHECKIDENT('Customer', RESEED, 1); -- add your modifications here UPDATE dbo.Customer SET ModifiedBy = 'TestUser', LastModifiedDate = '20100930' ;
Note that because DBCC CHECKIDENT has some gotchas that look and feel like documented bugs, we really need this try block in the script, otherwise we shall not be getting consistent results.

Alternatively, we can exclude the identity, ModifiedBy, and LastModifiedDate from testing, but that somewhat complicates the process of rolling out tests.

This post continues the series on unit testing, the previous post is How to Benefit from Unit Testing T-SQL: choosing what to test

The next post is How to Benefit from Unit Testing T-SQL: Reusing Manual Tests as Parts of Unit Tests

How to Benefit from Unit Testing T-SQL: choosing what to test.

Alexander Kuznetsov — Tue, 28 Sep 2010 21:04:00 GMT

When we write articles, blog posts, and books, our examples must be simple. On the other hand, when we write automated tests in real life, the most complex modules need automated tests much more than simple ones.

Consider, for example, the following trivial stored procedure:

CREATE PROCEDURE dbo.SelectCountry @CountryCode VARCHAR(3) AS BEGIN ; SET NOCOUNT ON ; SELECT CountryCode , [Description] FROM dbo.Countries WHERE CountryCode = @CountryCode ; RETURN @@ERROR ; END ; GO

Should we ever need to change this procedure, we can just go ahead and change it. With or without a unit test, changing this procedure would be very easy. More to the point, after changing this procedure we might have to change the corresponding unit tests, if they exist - we shall spend some precious time maintaining that unit test that has never been useful.

If, however, we are dealing with a complex module, unit tests may really help us out big time. Complex modules require more effort to maintain, and benefit more from unit testing, because:

Complex queries need better documentation, and a well written unit test works very well as such - it demonstrates how to run a module and shows the expected results;
Complex queries need performance tuning more often, and when tuning we may have to completely rewrite a slow query, improving its performance while keeping its functionality intact;
Complex modules are more likely to have bugs, and when we fix those bugs, we need to make sure that we haven't introduce other ones;
Complex modules are more difficult to change if the requirements have changed

Because usually any unit tests involving the database run much slower than unit testing C++ or C# code alone, we typically do not add as many unit tests as we wish - we must keep the overall time the test harness runs low, within acceptable limits. So keeping unit tests for trivial modules may be very counterproductive as our systems grows, as we get more and more unit tests, and as we begin to wait too much until all our tests complete.

However, there is a simple compromise that allows us to at least execute some procedures and do at least some verification of their results - we can use some stored procedures to populate test data. This way we do not explicitly test procedures used in building test data, but we test them implicitly, when we are getting expected results from the unit tests.

This is the second post in the series on unit testing. Here is the previous post.

The next post in this series is: How to Benefit from Unit Testing T-SQL: choosing what not to test

How to Benefit from Unit Testing T-SQL. Part One.

Alexander Kuznetsov — Tue, 28 Sep 2010 02:03:00 GMT

Unit testing may be extremely useful if we do it right – if. On the other hand, if unit testing is being used inefficiently, it can be a huge waste of time and effort.

When we were beginning to unit test our T-SQL more than three years ago, we wrote a series of articles describing how to do it with C# and NUnit. This approach worked out really well for us, and the effort spent on building our test harness paid off really well. However, when we were describing our approaches, we concentrated on how we did it, without too many explanations on why we made the choices that we made.

After unit testing my T-SQL for more than three years, I decided to describe the same approach from a different angle, explaining why and when we want to unit test T-SQL, why we want to test it the way we do, when unit tests really shine, and when they are all but useless.

When we develop unit tests as part of normal development cycle, we expect at least some of our effort to pay off – we are less likely to need maintenance, and if we still need it, proper unit tests will simplify it a great deal. To get the biggest return on our investment in unit tests, we need to:

Concentrate on situations when unit tests are most likely to be useful at some later time,
Concentrate on situations when developing unit is relatively easy – go for low hanging fruit first,
Develop and maintain unit tests efficiently, automating routine operations as much as possible,
Utilize unit tests as documentation, saving on documentation and maintenance costs,
Make sure everything that needs verification is verified – if we have loopholes in our tests, we may not notice some bugs,
Make sure the test results are presented in an easy-to-understand way, otherwise we can spend too much time interpreting them.

This is the preliminary plan. Soon it will expand into a series of blog posts, explaining these ideas in more detail. Here is the next post.

Survey: how do you unit test your T-SQL?

Alexander Kuznetsov — Mon, 03 May 2010 21:26:00 GMT

How do you unit test your T-SQL? Which libraries/tools do you use?

What percentage of your code is covered by unit tests and how do you measure it?

Do you think the time and effort which you invested in your unit testing harness has paid off or not?

If you do not use unit testing, can you explain why not?