Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Feb 24, 1999

Data Types

By Christopher Shaw

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.

 



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date