SQLServerCentral Article

Writing Maintainable Code


It seems to be that my lot in life is to be the "fix-it" person. I'm good at it, I enjoy it and those that sign my paychecks appreciate it. This requires me to plod through lots of code. Much of it is mediocre, some of it is downright terrible and there are also the occasional gems. I have found that there are some very basic principles which, if utilized properly, lead to a much more pleasant and productive development environment.

I know that coding standards can be a very touchy subject but I have learned that having at least a basic set of standards leads to much less headaches, heartaches and late night hair-pulling sessions. I also know that if the standards get too picky, they will become more of a burden than a help.

With that in mind, here are the basics that I have developed. Think of this as my 'wish list' as a visiting consultant. As there appears to be a natural division to the points I wish to make, my plan is to divide them into two separate articles. This first article shall cover the basic, generic principles which apply to any development environment. I shall cover DB-specific development principles in a separate article.

Note that these are not design standards. That's a completely separate topic and beyond the scope of what I wish to cover (at least for now that is).

1) Agree on a coding style for your workplace, document it, publish it and use it.

I honestly don't care what the style is as long as it follows the other principles. Sure, I have my favorite style, but I have found that the specific style does not matter, what does matter is that you simply have one. Code that is consistently formattedis easier to read and, consequently, maintain. A consistent style acts as a basic roadmap for reading code. It's also much easier to do cut and paste code


Of course, the style is useless if it is not properly documented and published. It's amazing how many times I've heard 'We have a standards document around here somewhere.' Or even better: 'We have a standard style that everyone just knows to use.' My suggestion is to make it part of the employee manual and have everyone who touches the code read it and sign it.

2) Note dependencies, what the code effects and the expected results.

What is the anticipated input? Does the code impact anything (delete records, change values, create objects)? From what is it called and is it expecting anything to be there? Does it call any other pieces of code? What errors will it throw and why? What is the end result?

Answering the above questions has two benefits. In the first place, it makes you think about the code you are writing and how it fits within the overall system. Think of it as another sanity check on the design. Additionally (and more importantly for the scope of this article) it makes the code significantly easier to maintain. If I have a reference for this information, I dont have to spend any significant time scanning the code to try and figure this out.

3) Use white space.

Which of the following is easier to read?

Select a.Col1, a.col2, a.col3, case a.col5 when 'A' then 'apple' else 'banana' end as FruitName 
from tablea a where a.col7 = 1


     case a.col5 
     when 'A' then 'apple' 
     else 'banana' 
     end as FruitName 
     tablea a 
     a.col7 = 1

It doesn't take any significant amount of time to format code. It does take significant time to try to decipher code that is cramped and jumbled. In fact, adding white space is the first thing I do when tackling new code.

4) Establish naming conventions.

This one is a tricky one and I'm of a mixed mind regarding it. In theory I think it's wonderful to have clearly identifiable names throughout the system but in practice, it tends to become more burdensome than helpful. As a workable compromise, these are the basics that I find useful:

  • Use full words, not abbreviations.
  • Do not use keywords / reserved words.
  • Make the name meaningful (i.e. @SalesCount rather than @MyCount).

5) Keep it simple.

It's an obvious fact, but looking through 50 (or even 500) lines of code is much, much simpler than slogging through 5,000+ lines of code. On the development side, it's easier to read, easier to separate out and easier to step through. If you have properly followed principle #2 above, any needed setup will be no problem at all. Small blocks of code are also much easier to test and deploy.

There is also the simplicity of the code itself. Stupid code tricks are fun (and I will play around with them from time to time as a good mind exercise), but if it's unreadable and difficult to follow, it has absolutely no place in a business environment. A basic rule of thumb: if you have to comment the code to explain what you did and why, you should seriously consider rewriting the code. (As an addendum to that, if you re-visit code you have written - which is a practice I highly encourage- and don't understand why you wrote what you wrote, it should probably be rewritten.)

To me these are all common sense, but it's amazing how few shops (both large and small) make any attempt to follow these principles. I hope that you find them useful and I'd love to hear what items you would add to this list (or take away) and why.

Stay tuned for part two: Database Developers' Principles.