Monday, April 25, 2011

When Is a Number Not a Number?

I've seen sample tables that have fields like Zip code or Social Security Number. These columns are invariably defined using an integer data type. I don't think this data is numeric; I think it's character data that happens to consist of all digits.

In my opinion, if an entity doesn't have arithmetic operations performed on it, then it shouldn't be defined as a number. 

Here's an example using SQL Server 2008 that demonstrates one of the pitfalls of using a numeric data type: leading zeros won't be displayed.  

 Zip2 VARCHAR(5) CHECK (Zip2 BETWEEN '00000' AND '99999')

INSERT INTO ZipTest VALUES(05678,'05678');


(1 row(s) affected)
Zip1   Zip2
------ -----
5678   05678

(1 row(s) affected)

These two values are obviously not the same.

So, to sum up (no pun intended): just because data looks numeric, doesn't necessarily mean it is numeric.

No comments:

Post a Comment