This editorial was originally published on Oct 7, 2014. It is being re-published as Steve is on holiday.
Do we need a guide for code like this one? The piece linked is from a programmer at Google who gives some code review items that should be tested for. I'm wondering we we might want to have something similar for T-SQL code?
I've given a few talks on how I to get started with tqslt, which is a great framework for unit testing T-SQL code. As I've learned more about it, and experimented, I've been amazed by how flexible it is and how it makes it easy to setup and run tests on T-SQL code. However I can only experiment with the way I code and the tests I think of, and I'm sure there are many other ways in which we can better verify that our queries work as expected.
Testing has changed since I developed software in VB and C++. It seems that much thought has been given to building better unit testing that can not only catch bugs, but also ensure the code performs as we expect it to. There's also the idea that we can run these tests in an automated fashion. That helps prevent some bug when we first write code, but it more often ensures code continues to work as we re-factor our work and enhance our applications. More and more I think that the constant and continuous regression testing is at least as important as initial testing, if not more so.
I really hope that we improve our development processes and testing methodologies for SQL code. It seems that it's an area where we can also increase the speed at we build better database applications, and improve the quality at the same time. If any of you are formalizing the testing of your T-SQL code, we would be interested in publishing your thoughts and results for others to learn from. Please feel free to submit an article.