T-SQL Tuesday #156 – Production Grade Code
T-SQL Tuesday is the brainchild of Adam Machanic (blog). Steve Jones (blog, Twitter) is currently maintaining this fantastic initiative.
This month’s topic has been chosen by Tom Zika (blog, Twitter), “Which quality makes code production grade?“
My Contribution
When I first thought about this topic, I didn’t have any clue what to write… Then, I started to think about what I’m always doing while I’m developing and eventually before I publish the code to production.
In the following paragraphs, I will be going through my “default” checklist of things that I always check and do when developing. The start with, when I’m developing Stored Procedures for data transformation loads, I always make sure that I have a short description op the top which explains on a high level what I expect the code will do. This doesn’t need to be very extensive but just a short description of the goal/ end result.
While developing, I tend to quickly write my code and as soon as I have my “prototype” finished, I start to make adjustments. The first step in making adjustments is to find out which hardcoded values I definitely should replace with parameters or even configured values in some kind of configuration table. As soon as this is done, I will continue to the next step.
As a next step, I start to format my code properly, if not already done during development, to make sure that everyone should be able to read it easily. This step includes giving proper aliases to tables and using these consistently. Getting unused columns out of the script, giving used columns a proper name and getting the indent right in the script.
When I’m happy with the formatting, I start to look at performance optimization, I start by checking if I may benefit from certain indexes, using temp tables…
As soon as the performance is fine, I get to my last step in the process which is adding logging after certain steps to make sure that I can debug more easily when things go wrong. The logging allows me to follow up on how long each step in the process takes and if the step fails to identify which step I need to take a look at.
As soon as all these steps are completed, I consider my code as production ready and push my code to Azure DevOps to get it to the development environment first and eventually to the production environment after testing.
In the future, I’m planning to get started with unit testing (probably tSQLt) as well, which will be an additional step to write the unit tests and get the code production ready.