At SQLServerCentral, we have a book on Defensive Programming in T-SQL. However not a lot of people have read it, or at least they haven’t downloaded it. That’s disappointing, as I think this is an area many of could improve our programming skills. Many of the techniques are simple things, or items to be aware of when writing code in order to make it more robust and reliable.
I ran across a piece on the Enterprise Craftsmanship blog als covering this topic. That post is geared more towards C# and other OOP languages, but some of the concepts are valid for any software system. Certainly the need to guard against invalid values, especially NULLs is important in much code, and especially important in SQL code. I hope all of you understand the impact of NULLs on aggregates as well as other T-SQL commands, operators, and expressions. If you have any doubt, be sure you read about the impact of NULLs in BOL and perhaps write tests that inject NULLs into your code.
There are places in our T-SQL constructs where we might want to develop code defensively. Checking parameters, using defaults, and watching out for ambiguous updates, and more. However, the place where I really think we want to be defensive is when we deploy changes to our production systems.
I’ve written about idempotent code before, and all of you that develop software should be familiar with the concept. Perhaps more importantly, you should practice writing code that is idempotent to ensure that you really can write code this way. While not all deployment code needs to be written this way, there are plenty of cases where we want to include some sort of guard clause to prevent unexpected changes during our deployment.
I’d encourage you to take a bit of time and examine the idea of programming defensively, practicing some of the techniques and making an evaluation of where this might improve your own code base. Who knows, maybe you can prevent some future bugs by learning a bit more today.