The Ten Rules of Formatting in SQL Server for Maximum Maintainability Part
And now for the exciting conclusion of The Ten Rules SQL Server formatting in SQL Server for Maximum Maintainability!
Name all variables and parameters to indicate not only function but also scope
It is easy to get lazy and name a variable or parameter in SQL like @temp or @string that doesn’t really mean anything to you after you come back to review the code. I recommended especially with SSMS having mostly a working IntelliSense (also tools abound that can give you similar functionality) there is no reason not to give your variables and parameters meaningful names.
Even if you give your variables meaningful names, there really isn’t a good way to tell whether the current variable you are trying to utilize is a parameter that was passed in or a variable declared and set after the proc has already been kicked off. I know that you can go check, but that becomes cumbersome especially for lengthy stored procedures. For this what I was taught was to give all parameters a prefix of p_ and for all variables give a them a prefix of v_. So instead of @searchString that is being passed in as a parameter, you’d use @p_searchString and the same concept for variables. Instead of @modifiedSearchString use @v_modifiedSearchString (you’ll see these variables being used later on).
Indent to indicate scope
With all of the conditional flows that happen within a stored procedure, it is important to make sure that all of the logic that goes together underneath an IF statement for example, is indented to indicate visually everything that will run when the condition of the IF statement is met. Personally, I us two spaces to indicate logical indent for a change in scope, but if you are consistent every time you can use whatever works for you (as long as it isn’t TAB’s, please see Rule #1).
Give aliases meaningful names
The topic of aliasing tables within a query is probably the most hotly debated topic on this list because everyone seems to have their own system or preference. I’ve seen the order mattering most to some developers causing them to adopt a system of naming the first table A, the second table B, etc. I’ve also seen those that will abbreviate the table name in the alias as well as those that choose logic names based on the tables function in the query. My personal preference (and this is what we are going to have to go on here because the guys that taught me differ from me here) is table abbreviations and use of logical names where it makes sense like during use of a derived table. I like this method for two reasons:
- When you go back to review the code the aliases mean something to you
- When someone else goes back to review the code they might mean something to him or her
Even though we strive to write clean SQL code that logically makes sense and in the way that we write it, it is self-commenting (See Rule #6 and Rule #8), everything that there is to know about a section of code or query can’t always be enough to explain what is going on. I’ve noticed that by and large, SQL developer aren’t be fans of comments and when they do add them they are short and usually at the top level like a header. Now I recommend always using some sort of a header for your scripts, but to get to that next level of maintainability put comments at the beginning of logic flow blocks (like by an IF statement) or at the top of a query to give the person reviewing the query a bit more information as to what the query does. I can’t tell you the number of times I’ve had to sit and read and re-read queries that I wrote because I slept a couple hundred times between the last time I look at the script.
Format/Re-format all SQL code you work with every time
Every time I write even a small query I format it and every time I get a script from another source to work on, the first thing I do is reformat it with all of the previous rules so going forward I can change and maintain it. Let’s take a look at this script I pulled from AdventureWorks:
The script isn’t too awful, but let’s take a look at it using all of our formatting rules now:
It took me about 15 minutes to re-format this script, but now it is much clearer how the script flows and where you could easily step in to create additional functionality. Writing readable, maintainable SQL code is hard work, but it is worth it because of the reward not having to spend hours yourself or someone else coming behind you figuring out what you did before and why it isn’t working now. The habits you create when writing SQL code will dictate the success of you and your team, so put in the work and you’ll see the results. Apply these rules consistently and you will find like I did, that you can spend more time getting work done and less time trying to figure out where things went wrong.