I had two opportunities this week to think about this post. The first was I opened a query from a co-worker that has several updates. The second was my daughter sent me a snippet of a query that had obviously been touched 4-5 times and clearly had different styles, and I only got to see the where clause.
First off, I am far from a code snob. My personal expectations for myself are stringent. But my expectations for others are a lot of losers because I won’t work to get it done. Hence, balance.
Style
Width, a lot of style guides suggest 80 characters. This standard came from the days of 12-inch monitors and people printing scripts. My monitors are 32 inches, and I have two of them. And I can’t remember when I the last time I printed a script. This means make it readable, but don’t worry about the width. Documentation, on the other hand, sticks with 80 characters.
Documentation
For documentation, I like a description and changelog that looks likes like the following:
The overall description is basically the purpose of the code. The change description is shorter. If more detail is needed, you can check your help desk software (Jira, boss, salesforce….). Affected table(s) and Used by are optional
For single-use code:
Repeatable code:
Production code:
Basically, the longer the life expectancy is for the code, the more comments are needed. In the single-use code, you will be the creator, editor, supporter, and executioner (to delete the code and run it). The repeatable you might hand the code off to another person, but more than likely, you will be the editor. On production code, it is no longer under your control at all. The code my daughter showed me was edited by no less than 4-5 editors, and that got messy quickly.
Comments
Each major block should have a short note about why it exists. There is a little more here. Within a SQL command, do not use –comment, use /* comment*/
/* */ Comments should end in –*/ This allows you to eliminate the /* and the code will function. It is ok to leave in the – comments in the /*… */ block.
when a comment is removed
Variables
Declares naturally begin with an @ sign. After that v_<name> For example @v_cnt
Parameters should be named @P_ For example @P_db
Debug
Start with @v_debug. Later in the code, use IF @Debug = 1 with PRINT and even to temp tables to log the results as you go. My start on this was from my oracle database days. I like to include this in all my production codes. But Brent Ozar explains this well in Quick Tips For Debugging Large Stored Procedures – Brent Ozar Unlimited® post.
Forced errors
When you write code that should be stepped through, start with an error:
Error traps
In production, the code makes use of RAISERROR and begins transactions. Just good coding. In the message, I like to include the server – system: message format. This helps in the clown care servers.
Testing
Always test each block (unit), then test the entire script.
Embed code in .net or use stored procedures
New systems do not have a full load of data, so it is hard to tune. But tuning in two parts, first the query, second the database indexes. To make this process easier and so you don’t need to recompile the application, store the code in stored procedures. This opens the door for tuning after the fact and when you have a bigger, better sample of data.
with inline code
with the stored procedure:
Execution plan
On the execution plan, I expect to just basic scan to make sure nothing is crazy wrong. We can turn it in later if needed.
“Single-use” queries are a big deal. Only waste your time if you can, or you might convert this later to the next two types.
The “repeatable queries” need more attention.
The “Production queries” (agent jobs, stored procedures, application code, and utilities) this is another story. This needs a lot of reviews and possibly the DBA to tune it. This is based on how often the code will be run.
I am working on another post that gives more details on execution plans, the properties list, and the developer version of reading them (light tuning). So, check back in a few weeks.
As your code evolves from single-use to repeatable to production, you want to add more commenting and details, improve your testing and take a deeper dive into the “execution plan.”
What we are talking about here is governance. You can make this into a crazy policing process. That will drive talent away and add a lot of work. Or you can make this easy, manageable, and something that does what your real goals are. What are your goals? Mine is to be able to read code when I need to and at a glance, with the needed information. So, what is your process, and what should it be?
Remember, the goal is about communicating with the people that follow you. In my daughter’s case, I suggested she write the creator and thank him for commenting on this code well, for beginning with, and learn from what she saw in the subsequent updates to the code. This was a good learning experience for her.
But from the governance side, try to remember too little governance creates a mess. Too much creates a stalemate. Find your balance and lead by example.
I Help make your databases; fast, reliable, secure, and with less downtime. How can I help you?
