Chapter 3 of the Defensive Database Programming Book by Alex Kuznetsov teaches us about how to “survive” changes to database objects. Alex brings to light how simple changes to the underlying schema could be damaging to performance and accuracy of the code that touches the objects to be changed.
Three main focal points are discussed in this chapter.
- Changes to the Primary or Unique Keys
- Changes to Stored Procedure signatures
- Changes to Columns
A key lesson is outlined in subtle terms at the beginning of the chapter. If you have assumptions based on any of the above focal points, then those assumptions must be documented! If this information or the assumptions are not documented, how can the changes made be properly tested? All assumptions must be tested and validated. If changes are made to the schema, any prior assumptions for the code touching the affected column, key, or signature must be reassessed, documented and validated.
Alex also brings up a a good point about Unit testing and how that can help speed things along when validating these assumptions. This also provides a gateway to a level of documentation for those assumptions.
In addition to Unit testing, Alex suggests a counter measure of using @@ROWCOUNT to ensure that only 1 row is updated in update procedures. If the rowcount is different than the expected 1 row, then the transaction is rolled back. In his samples for this, Alex uses IF blocks. I would have liked to see a TRY…Catch implementation as well. This method is less preferable to the Unit test. The reasons for that are explained in the chapter – have a read and find out why.
Further along in this chapter we learn another important lesson and a good practice to be using. When calling a stored procedure that has parameters, use the parameter names when calling the stored procedure.
From here, there are several other recommendations in this chapter worth reading. We see more best practices and further discussion elaborating particular methods over other methods. There are several good lessons to be learned from this chapter and to find the rest of them – you will have to read it. Enjoy reading!
