The first may seem really simple and even in some cases too simple.
Know your data
types. Let me explain. Numeric data types have to be numeric. You can not store the
letter "A" in an int. field. This is just the way it works. A character data
type does not mean you are limited to the letters A-Z. It may store numbers as well, and
in many cases it has to. One of the biggest mistakes that I have made on a database is
storing a zip code in an Int. field. When I designed the database that I was working on, I
thought my zip code is 90210, and that is in the range that can stored in an Int. field
data type. The problem lies where there are zip codes that have a preceding zero. Before I
knew it I had four digit zip codes in my database. What a mess that was to clean up!
Knowing your data types is just as important as knowing your data.
This theory can work both ways. At one time I stored numeric data in a
character field due to the ease of importing the data, as there were problems in the data.
This became a big problem about six months later. My boss came to me and said, "Hey,
wouldn’t it be great if we could do a search on this location number?" I started
thinking about it. I had mistyped this data. I could not deliver quick results on these
searches because of the data type. To make this search work as fast as it could I would
need it to do a conversion. This meant what I needed to do was create a new table with the
exact definition of the first table but with the change of the data type. Then I would
have to insert into, or select into and create the table on the fly, with the convert
statement in the code. After I was done then I had to rename the table so that all the
pre-existing stored procedures would still refer to the proper place. Then I had to start
running indexes on the second table. To finish it off I would run update statistics and
change all the stored procedures that affected that table. I considered myself lucky. I
had no triggers or constraints on that table.
These steps may not be the steps that others would have taken but these were the steps
that I had to do if I was going to get the job done correctly. The results are the same.
If I had thought about what I was doing ahead of time then I would not have had all this
additional work. I can tell you one thing I won’t, make this mistake again! I guess
that it was a lesson that I had to learn on my own.
