THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

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

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.

  Adding one column

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

Published Wednesday, October 27, 2010 5:00 PM by Alexander Kuznetsov

Attachment(s): adding_column.JPG

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement