Floating Point Numbers – SQL Smell

Floating Point - SQL Smell?
Floating Point – SQL Smell?

Do you use Floating Point numbers? Do you even know what they are?

Phil Factor regards Floating Point Numbers as an SQL Smell. That is to say “something which may cause problems”.

I’m going to explain why this is from an analyst’s point of view.

What are Floating Point numbers?

Floating Point - Normal versus Scientific or Engineering representations of numbers.
Floating Point – Normal versus Scientific or Engineering representations of numbers.

When I first started programming with FORTRAN we only had two sorts of numbers: integers and floating-point. You used integers for counting things and floating-point for calculations. It was that simple!

Modern languages and SQL databases allow other options, including precise decimal forms.

Floating point numbers use the binary equivalent of “Scientific” or “Engineering” notation. They stored differently to integers or decimals.
Their value is stored in two parts which are known as the “mantissa” and the “exponent”. This arrangement allows them to hold an enormous range of values very efficiently but at the expense of precision.

Problems with Floating Point numbers

Floating Point Arithmetic can cause loss of precision
Floating Point Arithmetic can cause loss of precision

Floating point numbers are intended for scientific or engineering calculations.

They can cause problems in normal business calculations.

  • Arithmetic can cause rounding.
  • Testing for equality can produce unexpected results.
  • Phil Factor does not like them being used in keys or indexes.

Size and range of Numbers

Size of FLOAT or REAL for different Precision values
Size of FLOAT or REAL for different Precision values

Floating-point numbers allow a very large and very small values. I cannot imagine examples of the extremes.

Size of DECIMAL (or NUMERIC) columns
Size of DECIMAL (or NUMERIC) columns

 

On the other hand, precise numeric data-types do not have the problems associated with floating point.

When should you use Floating Point numbers?

Some Very Big and Small Numbers
Some Very Big and Small Numbers

Very few businesses or “domains” need to use floating-point numbers. The table shows some example where they would be essential. In most cases these are estimates of extremely large, or very small, numbers.

Look at it like this: if you are working in engineering, astronomy or nuclear physics you may need floating-point numbers. You probably know if you really need them.

Summary

I recommend avoiding floating-point numbers. Phil Factor describes them as an SQL Smell which should be investigated. They are essential for some applications. When misapplied they will cause problems. If you think you need to use them, think hard.

Where next?

This is the concluding post in this sequence of articles. I’ve chosen to concentrate on potential problems which are likely to trouble analysts like me. Phil Factor’s original article identifies many more SQL Smells. These include details of design and programming. If you spend time working with SQL and specifying or designing databases, then I recommend the article both as background reading and as a handy reference.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.