So-called "exact" numerics are not at all exact!

Hugo Kornelis — Tue, 16 Oct 2007 22:42:00 GMT

Attempting to dispel myths tends to make me feel like Don Quixote, riding against hordes of windmills that won’t budge. In this case, even some of my fellow MVPs and Microsoft’s own Books Online are among the windmills…

Books Online says that there are two categories of numeric data types: “approximate” (float and real), and “exact” (all others, but for this discussion mainly decimal and numeric). It also says that “floating point data is approximate; therefore, not all values in the data type range can be represented exactly”, thereby suggesting that other numeric data types are capable of representing all values in the data type range. The latter is of course not true, for there is no way that values such as 1/3, π, or √2 can ever be represented exactly in any of SQL Server’s data types.

But Books Online is not the only one to blame – many respected MVPs carry part of the blame as well. For instance, Aaron Bertrand, the original author of the famous website www.aspfaq.com, write on a page about rounding errors when using floating point mathematics: “You should try to avoid the FLOAT datatype whenever possible, and opt for the more versatile, and precise, DECIMAL or NUMERIC datatypes instead”. And just today, I was reading this (otherwise impressive) book by Bob Beauchemin and Dan Sullivan, when I came across a passage that presented a code snippet to demonstrate rounding errors in the .Net equivalent of float; the authors did present size and speed as possible reasons to choose float over decimal, but failed to mention that decimal is not exact either.

Since reading this paragraph was the final straw that caused me to blog on this, I’ll start with a SQL Server equivalent of the code presented by Bob and Dan:

DECLARE @Float1 float, @Float2 float, @Float3 float, @Float4 float;

SET @Float1 = 54;

SET @Float2 = 0.03;

SET @Float3 = 0 + @Float1 + @Float2;

SELECT @Float3 - @Float1 - @Float2 AS "Should be 0";

Should be 0

----------------------

1.13797860024079E-15

DECLARE @Fixed1 decimal(8,4), @Fixed2 decimal(8,4), @Fixed3 decimal(8,4);

SET @Fixed1 = 54;

SET @Fixed2 = 0.03;

SET @Fixed3 = 0 + @Fixed1 + @Fixed2;

SELECT @Fixed3 - @Fixed1 - @Fixed2 AS "Should be 0";

Should be 0

---------------------------------------

0.0000

As you see, adding some numbers and then subtracting them again does indeed incur a rounding error. The result is 0.0000000000000011379786 instead of 0. But what happens if we do a similar test with multiplying and dividing? The code below should always return 1. It does for the floating point calculation, but not for the fixed point version – this one’s result is off by exactly 1E-15, approximately the same margin of error that float caused when adding and multiplying.

DECLARE @Float1 float, @Float2 float, @Float3 float, @Float4 float;

SET @Float1 = 54;

SET @Float2 = 0.03;

SET @Float3 = 1 * @Float1 / @Float2;

SELECT @Float3 / @Float1 * @Float2 AS "Should be 1";

Should be 1

----------------------

DECLARE @Fixed1 decimal(8,4), @Fixed2 decimal(8,4), @Fixed3 decimal(8,4);

SET @Fixed1 = 54;

SET @Fixed2 = 0.03;

SET @Fixed3 = 1 * @Fixed1 / @Fixed2;

SELECT @Fixed3 / @Fixed1 * @Fixed2 AS "Should be 1";

Should be 1

---------------------------------------

0.99999999999999900

It even gets more interesting when you change the value of @Fixed2 from 0.03 to 0.003 – in that case, the floating point calculation still runs fine and without error, whereas the fixed point calculation bombs:

Msg 8115, Level 16, State 8, Line 11

Arithmetic overflow error converting numeric to data type numeric.

Should be 1

---------------------------------------

NULL

Now I’m sure that many of you will already have experimented and found that they could “fix” this by increasing the scale and precision of the fixed point numbers. But they can never exceed 38, and it’s not hard at all to come up with examples of rounding errors in fixed point calculations for any setting off scale and precision.

Mind you, I am not saying that float is “better” than decimal. It is not – but it’s not worse either. Both “exact” and “approximate” numeric data types have their place. A grand choice for “exact” numeric data, is when dealing with numbers that have a fixed number of decimal places and represent an exact amount, such as monetary units. There’s no way that I would ever use floating point data in such an application!

But if you are dealing with scientific data, that is usually derived from some measurement and hence by definition an approximation of reality (since there’s no way to measure with unlimited precision), floating point data is an excellent choice. Not because it’s approximate nature mimics the act of trying to get a measure as close as possible to reality, but also (or maybe I should say: mainly) because it can easily represent both very large and very small numbers with a large number of significant figures – try for instance to do something like this with “exact” numeric data types, if you don’t believe me!

DECLARE @Float1 float, @Float2 float, @Float3 float, @Float4 float;

SET @Float1 = 987654321.0 * 123456789.0;

SET @Float2 = 0.123456789 / 998877665544332211.0;

SET @Float3 = 1 * @Float1 / @Float2;

SELECT @Float3 / @Float1 * @Float2 AS "Should be 1";

Should be 1

----------------------

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server' : Mythbusting

So-called "exact" numerics are not at all exact!