T-SQL Tuesday #21 – Oh crap…

This weeks SQL Tuesday, hosted by Adam Machanic (blog, twitter) himself, is about crappy things from our past...

The first thing that popped into my head when reading Adams post was a story about a hack that, while I did not cause it, I spent a good piece of time at a previous employer trying to deal with the crap that came out of it.

The story starts, as so many stories do, with a database.
This database contained a set of lookup values in a single table. These values were needed all over the application driving business logic and display parameters. These values were also used in almost all reports. Most reports needed to look up several values in this table, so Joins, joining 20+ times to this table were common. But I did not start this story to talk about database performance. What I want to talk about was this comment in the JAVA code of the application, the part of it that was dealing with those lookup values. The comment was: "This is a hack and should never be deployed to production!". It was leading on a bunch of code retrieving those lookup values from an XML file. — I can hear you now say: "A what? I thought the values were in the database..."

Needless to say that this code made it into production and caused a double maintenance nightmare. I tried for several years to convince the money controlling powers that cleaning up this mess would actually save a lot of money, but there was always something more important to be done.

By the time I left the company that code had been in production for ~ 10 years, costing the company several tens if not hundreds of thousands of dollars. As far as I know it is still in there...

So, the next time you are working on a quick hack to save some time right now I would like you to hold on. I would like you to think about the cost associated with this short cut. Think about what happens if this code starts settling its ugly roots in your production environment. After that turn around and implement the right thing. And if your boss comes breathing down your neck about your decision to do the right thing, send him to read this story.

—

If you would like to read more about good coding practices, I can recommend the book Clean Code: A Handbook of Agile Software Craftsmanship.
While it focuses on JAVA, there is a lot of good information in there about how to write code so that it is easily readable and maintainable. A lot of it can be used in the SQL Server environment too.

The spirit of this book can be found on the inside title page:

Writing clean code is what you must do in order to call yourself a professional.

There is no reasonable excuse for doing anything less than your best.

—