Streaming Event Data through RabbitMQ to a Micr osoft Fabric Eventstream (2024-07-12)

The Number that shouldn't be a number

This editorial was originally published on Feb 2, 2015. It is being republished as Steve is on holiday.

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).

Gail Shaw

Join the debate, and respond to today's editorial on the forums