SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Number that shouldn't be a number

By Gail Shaw,

Sometimes I get really confused by the data types people choose for their tables. Take this example from one of the popular SQL forums:

Why, why, why did they choose an integer for the telephone number? Sure, it's probably ultimately numeric, but does that really mean it should be an integer? Is it likely that the phone number needs to be added to something? How often do need to find the square root of a phone number? Or invert one? Is one ever likely to need to do something like this:

log2(sin(TelephoneNumber2)/pi)?

If so, I'd certainly love to see the reason.

Telephone numbers, despite being composed of numeric digits, are not numbers. They are strings. They are not likely to be manipulated mathematically, and leading zeros are meaningful.

I've seen similar strange choices with postal codes, which in South Africa are four digits long, and where leading zeros are, again, meaningful. Cape Town's postal code is 0001, not 1. Storing a postcode in a SMALLINT is possible, but not necessarily a good option.

My general rule is that if the column isn't going to be mathematically manipulated, it's probably a string, not a number.

I remember the pain of working with a system where a developer had chosen the TIME data type to store the number of hours worked in a day. Anyone want to figure out how you add two TIME columns together? I'll wait…

I've also seen BIGINT used for the number of open orders that a customer has (optimistic), INT for dates, FLOAT for dates, NVARCHAR(MAX) for a true/false column. The list is endless.

Here is my call to arms: if you're designing a table, think about the domains for each of the attributes, think about what the maximum and minimum values can be, and then think very carefully about the best data type for that attribute. And don't get fancy. For financial data, while it might be possible to convert the value into hex and store it in a Binary column, storing it in a Numeric is probably easier.

Finally, what are the weirdest data type choices you've seen?

Gail Shaw (Guest Editor).

 
Total article views: 324 | Views in the last 30 days: 1
 
Related Articles
FORUM

CONVERT and Concatenate NUMERIC to date in Derived Column

I have to convert and concatenate 3 seperate numeric columns into 1 date column in a data flow

FORUM

How To Alter a datetime column to Numeric

How To Alter a datetime column to Numeric

FORUM

convert varchar to numeric

convert varchar to numeric

FORUM

Storing Negative Numbers

Why is my company coining a "best practice" to NOT store Negative Numbers?

FORUM

Great number of Stored Procs, or just client queries?

Doubts about the balance between having big number of stored procedures or allowing client apps to q...

Tags
database weekly    
datatypes    
editorial    
 
Contribute