What T-SQL data type would you typically use for weight and length?

Question

I am designing a table that has several fields that will be used to record weight and lengths.
Examples would be:
5 kilograms and 50 grams would be stored as 5.050.
2 metres 25 centimetres would be stored as 2.25.

What T-SQL data type would be best suited for these?
Some calculation against these fields will be required but using a default decimal(18,0) seems overkill.

Answer 1

It really depends on the range of values you intend to support. You should use a decimal value that covers this range.

For example for the weight, it looks like you want three decimal places. Say you want the maximum to be 1000kg then you need a precision of 7 digits, 3 being behind the decimal point. This gives you decimal(7,3)

Answer 2

Don't forget to put the units of measure in the column name, e.g. WeightInKilos, LengthInMetres

Answer 3

The best datatype depends on the range and the precision of weights and lengths you'd like to store. For storing people's weight, that would be between 0.00 and 1000.00 kilograms. So you'd need 6 digits most (precision=6), with 2 numbers behind the dot (scale=2). That's a decimal:

weight decimal(6,2)

For normal (non-scientific) use, I'd avoid the approximate number formats float and real. They have some surprising gotcha's, and it's hard for end users to reproduce the results of a calculation.

Answer 4

You need to take inventory of what things you are measuring. If they are measurements of UI windows then integers of pixels would be just fine. But that would not work for holding the measurement of the mass of a proton. It is the old tale of scale and precision.

The easiest solution might be to standardize them all to one unit of measure. As harriyott said you could add that to your column name. I'm not a huge fan of that due to flexibility in refactoring later if requirements or designs change, but it is an option)

If these measurements are wide open and general such that you need to support very large to very small numbers, maybe the measurement could be split into two columns. One to hold the magnitude and one to hold the unit of measure. One of the biggest downfalls to this would be comparing values if you need to find the heaviest objects, etc. It can be done with a lookup table, but certainly adds a level of complexity.