SQL Database choosing data types and not taking the quick way

So many times I’ve found myself in a situation of choosing getting things done faster over getting them done the right way.  This isn’t an innate philosophy of mine but rather one imposed upon me by deadlines, sense of urgency or even emergency, and on occasion it just ends up being situation of “I’ll do it this way for now and fix it up properly later when I have more time”.

Right now I’m referring to creating new columns in SQL Database tables vs using an existing column that was designed to be used for multiple purposes.  The problem with falling back on those oh-so-useful cover all columns is they’re often very large datatypes that can encompass multiple uses such as large VARCHAR fields.  Any time you find yourself using one of those columns to represent a number that could easily fit in an INT field, you should definitely probably do it the correct way the first time.  Searching VARCHAR fields in WHERE clauses is much slower and any indexes created on a cover all field will be much larger and slower to update.  A regular INT datatype covers numbers ranging from -2^31 … 0 … 2^31 – 1 and only uses 4 bytes to store the number vs 4 to 8 bytes per character in a VARCHAR field.  For an example, the number 1,234,567 could occupy 4×7=28 bytes or even 8×7=56 bytes if it’s a unicode NVARCHAR field when it could be represented using only 4 bytes in an INT field.  Storage isn’t the concern usually. Who cares if a table uses 100 Megs instead of 20.  It’s more about the search performance. Doing SELECT foo FROM tbl WHERE some_varchar_field LIKE number will go far slower than the same query using the integer version SELECT foo FROM tbl WHERE some_int_field = number.

So, try as I might to follow the Knight’s Code to do something right if it’s worth doing at all, I have found myself taking short cuts from time to time in the interest of saving time which later turns out to lose more time than the initial amount of time it saved in writing the system.  I hope this can serve as a reminder to myself, if nothing else, to take a few extra minutes in those moments of rapid development to get it done right the first time.

Hello world!

Let me introduce myself. My name is Milo La Mar and I’ve taken on the life of a programmer in a quickly evolving yet slowly tapering world of the internet during the early twenty first century.  I am a husband and father of two reality checking children who keep me grounded in a good way.  After all, it’s easy to drift in the wind and get lost in the details as a programmer.  I wont write about my location right now because my wife and I have become more or less nomadic since we both work remote jobs and can’t decide exactly what it is we want in a living situation.  But I may occasionally write about location and personal life outside of work and professional interest in blog posts.

I hope my writing serves purpose in the professional and theoretical programming world as well as in general public interest of day to day life as a working-from-home family dynamic.