The Simple, Lightweight Testing
Framework for Microsoft SQL Server
by Rob Garrison
TSqlTest is a simple, lightweight testing framework for Microsoft SQL Server. It utilizes just two technologies you are already familiar with: T-SQL scripts and .bat scripts. It is intended to be used as part of a continuous integration environment.
TSqlTest can be used to test stored procedures, triggers, functions, and database "state".
There is nothing magical about the framework. It came about after I had looked at the available options (mainly NUnit and TSQLUnit) and found the distance between zero and functioning unit test too great.
I could see easily how to script tests for INSERT, UPDATE, and DELETE procedures. The problem was validating SELECT procedures. The "ah hah" moment came when I was reading about "INSERT INTO EXEC " for a completely unrelated project. That was the answer. Now I could build the complete framework.
That was early 2001. I've been writing automated tests and refining the framework ever since. I actually refined it even more to publish version 1.0 here, and I plan to continue refining it as I get feedback from people using it.
As I write this, Microsoft is about to release Visual Studio for Database Professionals which will include some great unit test capabilities. There are many differences between their unit test framework and TSqlTest. One significant difference is that TSqlTest is free.
So, check out all your options, but whatever you choose, write unit tests for your database code. Once you start, you will be "test infected" like me.
Also, please provide feedback about the framework via e-mail or through my blog. I am hoping to learn a great deal from users so that I can both improve the framework and improve my own unit test development.
Thanks: I originally developed this framework as a part of my work at Corillian. I want to thank the company and especially Dr. Tom Cook for his support of my efforts.
Next steps:
- Add a "remove" script
- Build in a simple coverage tracker
- Publish Powershell script examples
TSqlTest can be used to test stored procedures, triggers, functions, and database "state".
There is nothing magical about the framework. It came about after I had looked at the available options (mainly NUnit and TSQLUnit) and found the distance between zero and functioning unit test too great.
I could see easily how to script tests for INSERT, UPDATE, and DELETE procedures. The problem was validating SELECT procedures. The "ah hah" moment came when I was reading about "INSERT INTO EXEC " for a completely unrelated project. That was the answer. Now I could build the complete framework.
That was early 2001. I've been writing automated tests and refining the framework ever since. I actually refined it even more to publish version 1.0 here, and I plan to continue refining it as I get feedback from people using it.
As I write this, Microsoft is about to release Visual Studio for Database Professionals which will include some great unit test capabilities. There are many differences between their unit test framework and TSqlTest. One significant difference is that TSqlTest is free.
So, check out all your options, but whatever you choose, write unit tests for your database code. Once you start, you will be "test infected" like me.
Also, please provide feedback about the framework via e-mail or through my blog. I am hoping to learn a great deal from users so that I can both improve the framework and improve my own unit test development.
Thanks: I originally developed this framework as a part of my work at Corillian. I want to thank the company and especially Dr. Tom Cook for his support of my efforts.
Next steps:
- Add a "remove" script
- Build in a simple coverage tracker
- Publish Powershell script examples
 |
Rob Garrison is the OLTP Data Architect for WebMD Health Services in Portland, Oregon. He has over twenty years of IT experience, with more than half that time in database design and development. |