Antipattern: Use FLOAT Data Type
Explore how the FLOAT data type stores fractional numbers in SQL, why it causes rounding errors due to finite binary precision, and how these errors impact calculations. Understand the implications for equality checks and aggregates, and learn why FLOAT is risky for financial or high-precision data. This lesson helps you identify when to avoid FLOAT and choose more accurate data types for your database.
We'll cover the following...
Most programming languages support a data type for real numbers, called float or double. SQL supports a similar data type of the same name. Many programmers naturally use the SQL FLOAT data type everywhere they need fractional numeric data because they are accustomed to programming with the float data type.
The FLOAT data type in SQL, like float in most programming languages, encodes a real number in a binary format according to the IEEE 754 standard. We need to understand some characteristics of floating-point numbers in this format to use them effectively.
Rounding by necessity
Many programmers are not aware of a characteristic of this floating-point format: not all values that can be described in decimals can be stored in binary. Out of necessity, some numbers must be rounded to a very close value.
To give some context for this rounding behavior, let’s look at rational numbers that have repeating decimal numbers, such as one-third, written as “0.333…”. The true value cannot be represented in decimal because we must write an infinite number of digits. The number of digits is the precision of the number, so that a repeating decimal number would require infinite precision.
The compromise is to use finite precision, which is choosing a numeric value as close as possible to the original value, for example, “0.333”. However, this means that the value isn’t the same number that we intended.
...