T-SQL Tuesday on Wednesday

This month’s T-SQL Tuesday has been moved to Wednesday.  The topic this month is “crap code”.

This is a great topic to showcase what not to do and see some actual bad code.  After all we tend to learn best from our mistakes.  However, I’m not going to show bad code.  I want talk about code that may or may not be bad, but becomes bad by virtue of ignoring outside factors.  What I am talking about is being a well-rounded DBA and making sure you take off the blinders and look at things from a 1000 foot view.  Yeah it’s time to put on the big boy pants!

Let me explain what I mean with a single example that has two repercussions.  Let’s say you’ve written this beautiful piece of T-SQL and followed all the general rules.  You didn’t use functions that cause table scans, you didn’t use a ” SELECT * “, and you have a granular ” WHERE ” clause.  You’re proud of the code and everything seems fine until you run it and it returns results in an unacceptable amount of time.

Why is it slow?  I mean this is a beautiful and fantastic piece of code you wrote!  Well you didn’t think about how the table was indexed and although you had a granular ” WHERE ” clause the table has millions of rows and the column in your clause doesn’t even have an index on it.  Yeah that just happened.  You were so focused on the query you forgot to think about the outside factors, your indexes in this case, that have had a very profound effect on your query.  We have to remember to take a step back and think about the outside factors that can affect our code.  In this case the code was perfect and all you needed was a proper index.

The other repercussion to this example, is to remember that it is not a “set it and forget it” kind of thing.  Let’s say you added the index and the query is now both genius and lightning fast.  That’s great for now, but what about six months or a year down the road?  Maybe another administrator made some indexing changes, or maybe you changed the index to satisfy another query.  Now the query performs badly, so what we need to remember is to periodically check our systems to make sure things have not changed.  We also need to be sure when making changes that they don’t adversely affect other operations on the system.