T-SQL Tuesday #50 is hosted by @SqlChow (b|t).
This month's topic is "Automation, how much of it is the same?".

Test Automation

Introduction

There are several types of tests that are useful during software development: Performance Tests, Unit Tests, Usability Tests, Acceptance Tests, Penetration Tests and Integration Tests to name just a few. In this post I am going to concentrate specifically on unit testing. I will show some of the benefits of automated testing and how unit test automation can transform your software development experience.

I will not go into detail on the other types of testing. However, they can greatly benefit from automation as well.

Unit Testing

A unit test is a test that concerns itself with a unit of work. In software development that is usually a single module. In an object oriented language, a unit test would test a single class. In T-SQL a unit test typically covers a single stored procedure or function.

Test Automation explained

When I automate unit tests my goal is to achieve full automation. I would like to be able to press a single button or at least call a single procedure to run all my tests end get a final unambiguous report how many of the tests ran successful (passed) and how many did not produce the desired result (failed).

To get to that level of automation you need to use some kind of testing framework like tSQLt. tSQLt is open source and you can download it at http://tSQLt.org. That site also gives a lot of resources to help you get started with T-SQL unit testing.

Test Automation however does not come for free. You need to plan your tests. Each test needs to set up its environment to make sure it is repeatable and does not rely on other tests to run successfully first. Each test also needs to contain code to check if the desired outcome was achieved. Let's look at, why spending that extra effort is worth it in the long run.

1) Un-Ambiguity

If you have a long manual test scenario catalog and each scenario requires you to manually compare the outcome of the test run, it is easy to miss a slight behavior alteration. Also, it is not always clear what plain English descriptions mean. E.g. the word "between" might be meant inclusive or exclusive of the boundaries.

With automated self-asserting tests you get an un-ambiguous result. The test is either passing or it isn't. There are no questions and you can't accidentally pass by a failure. So you will always know where you are at.

2) Easy Execution

Software development follows a feedback cycle. You develop some piece of functionality and, through whatever means, you will get feedback on its performance eventually. In big projects this feedback often comes after a long time, when the customer found a defect. Then considerable amounts of effort are required to re-learn what the code is doing, identify the problem and finally fix it.

If we could get the feedback that something is broken early on, preferable within a few minutes, the effort to fix the problem is greatly reduced as we still remember what we just did.

Test automation can help us shorten the feedback cycle. If we can run all our tests by pressing a single button we are much more likely to run them often. The shorter the time between test executions, the shorter is also the feedback cycle for everything we have done to the code base in between.

3) Fast Execution

This is similar to easy execution in that it allows you to run your tests more frequent, shortening the feedback cycle.

There is no question that an automated test will run a lot faster than a manual test. Automated tests also tend to be more focused which in turn can reduce their runtime further.

4) Complete Execution

How often have you made a change in a complex piece of software to implement a new feature or remove a defect, only to learn later that this change reintroduced an old problem or alter the software's behavior in a seemingly unrelated area? And again, if we only could shorten the time between making this mistake and discovering its ill effects, we would be a lot better off.

Test automation can help here too. If running all test cases is as easy (or easier) than executing a specific one, you are much more likely to run all test cases after a change. If you have good test coverage, running all test cases will likely discover the newly introduced problem right away allowing you to address it while the causing change is still fresh in your mind.

5) Continuous Integration

Continuous integration (CI) is a process that builds the complete system from scratch and runs all tests on it to insure everything is still in working order. This allows you for example to react to every check-in to source control automatically to build the software and run all tests on all target system versions.

CI gives you instant feedback that the software is not only working on your development machines but also on other machines and other platform versions. If you are writing software that needs to be able to execute in SQL Server 2008 and SQL Server 2012 this can be very helpful, again because it shortens that feedback loop. CI is not possible without automated tests.

Summary

Automated testing has many advantages over manual testing. Those advantages over time clearly outweigh the disadvantages like the additional effort it takes to develop an automated test. Some of the many benefits were listed above. All however have their roots in one place: The shorter the time to discover a defect, the cheaper it is to fix it. Running your automated tests often will help to reduce the time it takes to discover defects.

Call to Action

Download tSQLt today and see how automated testing can help you improve your software.