T-SQL Tuesday #21: Bad Decisions

Howdy folks and Happy T-SQL Tuesday Wednesday! Contrary to popular belief, both myself and my blog are not dead, it's just that preparations for my upcoming wedding have taken up nearly all of my spare time. Once I return from my honeymoon I hope to be back to blogging as usual!

This month's topic comes from none other than the founder of the T-SQL Tuesday movement, Adam Machanic (blog | @AdamMachanic). Adam's asking us to write about what he so eloquently describes as "crap code". We've all made poor coding and/or design choices at one pont or another, and even if it seemed like a good idea at the time it might still look bad in hindsight because we've all learned better ways to do things as we have progressed in our careers. This topic left me faced with a tough decision as I've definitely made my share of bad decisions whether I knew better at the time or not. I decided to go with an oldie-but-goodie that still haunts me to this day.

A while back I wrote about how my first job working for a city is what got me started working with databases. I worked for the building department and designed a database for storing information about building permits, contractor licenses and inspections. The decision to learn how to build such a database was a great one, but a few of the other decisions I made shortly thereafter were much less than great when I look back on them now.

I chose the wrong RDBMS

When I was getting started back in 2002 I was trying to decide between using Microsoft Access and MSDE 2000. I chose Access since it had a GUI, and of course it had to be better since it had a GUI, right? Since this application is still in use and I still maintain it, it goes without saying that I'm now kicking myself. MSDE may have caused me a bit more pain up front, but the upgrade path to SQL Server would have been much simpler than it is from Access, which is why I haven't bothered to convert it yet.

I tried to be fancy

In Access if you want to create a table with an identity column, you use a datatype called "AutoNumber". AutoNumber columns have 3 options:

– an incrementing value (like an identity)
– a completely random long integer value
– a replication ID (GUID)

Which did I pick? Surprisingly not the GUID, but I did pick the completely random value, which isn't much better. Fortunately all of my AutoNumber columns contain key values that are never seen in the application, but every time I open up a table I'm horrified at the randomness I see. Why'd I pick it? I really don't know. I have a strange feeling my high school brain thought random values would be way cooler than incrementing ones. Obviously I hadn't learned the concept of index fragmentation yet.

So there you have it – 2 decisions I made that seemed like good ones at the time yet turned out to be anything but. If I were re-writing this system from scratch it would be way better than it is now. Thanks, Adam, for the most excellent topic!