Database Reference
In-Depth Information
No Data Integrity Constraints
If we use varchar for attributes without considering the actual data, we
could have data integrity problems. The main reason we use data types is
to enforce data integrity. If we are storing a date, the datetime data type
ensures that the entered information is a valid date. Storing a date in a var-
char could lead to problems. For example, what if the application code has
a glitch that omits the first “/” and writes the date as “35/2007”? You might
say, “So what? We can fix that with a script,” but can you really fix it? Take
the date January 13, 2005, as an example. If that gets stored as 113/2005,
you have a problem. It could be converted to 1/13/2005 or 11/3/2005. Had
we used the datetime data type from the start, we would have received an
error when the application wrote 113/2005, and the problem could have
been found and fixed. There are literally hundreds of examples, but we
think you get the point: Use the correct data type, and you are on your way
to having solid data integrity.
The Need to Convert Data
Data stored in varchar attributes is, not surprisingly, stored as a string. This
means that it is stored, referred to, sorted, and grouped as a string. Why is
this an issue? Let's look at the problem you encounter when sorting. When
you sort strings, they sort alphabetically, and numbers sort numerically.
This can lead to very different results. To illustrate this, we created a table
called SortTest using the following code in SQL Server.
CREATE TABLE SortTest(
StringDate varchar(50),
RealDate datetime)
As you can see, we have two columns: one varchar and one datetime. Now
we insert all the dates for January 2007 in the format “1/1/2007” into this
table. Here are the insert statements we use.
INSERT INTO SortTest VALUES('1/1/2007' , '1/1/2007')
INSERT INTO SortTest VALUES('1/2/2007' , '1/2/2007')
INSERT INTO SortTest VALUES('1/3/2007' , '1/3/2007')
INSERT INTO SortTest VALUES('1/4/2007' , '1/4/2007')
...
INSERT INTO SortTest VALUES('1/31/2007' , '1/31/2007')
Search WWH ::
Custom Search