Why does using a Real data type in a mathematical equation return an incorrect result?

I was trying to figure out the source of what appeared to be a rounding error today, and discovered that one of the columns used in a mathematical equation was using the real data type, and this is causing my equation to return an incorrect value.

Can someone explain to me why

SELECT 776.2384 * cast(100 as real) / 100

equals 776.2385 while

SELECT 776.2384 * 100 / 100

equals 776.2384?

See Using decimal, float, and real Data, and Data Type Precedence

In your second query, 776.2384 is a decimal number (and so are the two other values). So the calculation is done in that type, and the result is mathematically exact.

In the first, you introduce a real. The other literals are promoted to that type, and the calculation is done according to IEEE 754 rules, in round up mode. IEEE 754 floating points are not exact, so you will generally not obtain the "mathematically exact" result you're expecting.

Quote from the articles above:


Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types.

StackExchange Database Administrators Q#24263, answer score: 4