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
———————-
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
———————-
1
15 Comments. Leave new
Decimals are exact when it comes to addition, subtraction and integer multiplication. If you divide two decimal numbers (or integers for that matter) the result is always a real number, which can only be approximated by float or decimal.
Hi Will,
For addition and subtraction, you are right (though exact numerics have a higher chance of running into overflow than approximate numerics).
For multiplication, you are not – if you multiply two NUMERIC(38,37) or NUMERIC(38,0) inputs, you would need a NUMERIC(76,74) or NUMERIC(76,0) to store the result without the possibility of precision loss, and that is well beyond the limits of SQL Server. (Note that multiplying float will also result in loss of precision at the far end of the decimal, though here, again, the chance to get overflow is lots smaller).
Just to prevent muisunderstandings, I’m not saying that float is "better" than numeric, just that both have their place. The reason for my (deliberately provocative) post is to fight the all too common misunderstanding that one should always choose numeric.
Best, Hugo
Hello
OK I agree if you have overflow because you are trying to store a value that is too large for a datatype, then all calculations causing the overflow will fail. So I should have said decimals and integers are exact with regard to addition, subtraction and integer multiplication as long as the result does not exceed the limits for the datatype 🙂
I would also agree no datatype is intrinsically better than any other, they are different because they have different purposes. Interestingly early basic used floating point as its only numeric type!
Fixed has an scale of 38 digits, float only 15 significant figures.
Of course, we could store them all as strings and shift all math onto the client.
Will: No datatype being intrinsically better than others, but each having their own purposes – thanks for writing this! It is exactly the point I am trying to make!
gbn: Fixed has indeed more precision, but with the limitation that you need to know in advance what order of magnitude your numbers are. You could in theory use fixed to represent maximum speed of different makes of automobile (though there is currently no way to actually measure them with a precision that would go beyond the 15 decimals of float). But if you have to store speeds without knowing in advance whether the measurements will relate to cars, photons, snails, or the growth of a tree, float gets a lot more attractive.
Storing them as strings and doing math at the client doesn’t strike me as a very attractive option – although I did once work with a language (REXX) that did indeed store numbers as strings and used its own math libraries to achieve a precision that was only limited by the storage capacity of the machine.
Best, Hugo
Thanks for this tutorial. 🙂
I can give you an example in which both float and decimal would fail misserably. Where the result should be 1, but both float and decimal of any precision will return a result of over negative 1 billion. So neither are immune to problems with errors.
Doug: I am very interested to see an example where such huge errors occur, and in both decimal and float. I’ve never imagined errors of that order of magnitude to be possible! Can you post the code?
However, your final sentence, "neither are immune to problems with errors", is exactly the point I was trying to make with this post. Thanks for reaffirming that point!
I got a small error, can post it here or email it to you?
Hi, I’m still strugling with multiplying float with numbers as in example below. The first resultset is in type real, the second in type float. In this example my price = 22.23 and the factor 100000. In the real-example the result is correct, but in the float-example I would expect that the result would be 2223000, but it is 2222999.95422363, rounded to 2 decimals it’s 2222999.95. And that’s not correct. Any ideas how to solve this problem? Should I round to 2 or 4 decimals first and then multiply? Is there a general solution?
Thx.
declare @tReal table
(col1 real)
declare @tFloat table
(col1 float)
declare @Factor int
select @Factor = 100000
insert into @tReal values (22.23)
insert into @tFloat select col1 from @tReal
select col1 as Contents,
col1 * @Factor as MultiplyFactor
from @tReal
select col1 as Contents,
col1 * @Factor as MultiplyFactor
from @tFloat
Hi Peter,
Yes, there is a general solution. And I even mentioned it in my blog. See this quote:
"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!"
Do not use floating point datatypes to represent money.
Do use floating point datatypes to represent scientific numbers (especially measurement results and calculations based on them).
Thanks for the great article. I like your analysis on when to use the "exact" and "approximate" data types.
I just noticed something in the multiplication and division example that may explain the variance for the decimal data type. It had to do with how the numbers were transposed to display the expected results.
Below are the revised queries. I took the liberty of revising the precision of the @Fixed3 variable so that it works with the 0.003 example.
Let me know your thoughts on this. Hope this helps
DECLARE @Float1 float, @Float2 float, @Float3 float, @Float4 float;
SET @Float1 = 54;
SET @Float2 = 0.03;
SET @Float3 = 1 * @Float1 / @Float2;
SELECT @Float3 * @Float2 / @Float1 AS "Should be 1";
Should be 1
1
DECLARE @Fixed1 decimal(8,4), @Fixed2 decimal(8,4), @Fixed3 decimal(16,8);
SET @Fixed1 = 54;
SET @Fixed2 = 0.03;
SET @Fixed3 = 1 * @Fixed1 / @Fixed2;
SELECT @Fixed3 * @Fixed2/ @Fixed1 AS "Should be 1";
Should be 1
1.00000000000000000
I found floats usable for any application including accounting systems. One should of course take care of rounding when displaying data and deeply think about relational operators. The inaccuracy is known and can be a problem when using very big / small numbers. There is a lot of old applications using floats without any problem.
The pitfall when it comes to decimals is unpredictable resulting precision of arithmetic expressions. Every seemingly clear combination of operators must be analyzed. Another problem is missing HW support for native decimal arithmetic.
Explain this one:
declare @quant real
set @quant = 37731.8
select @quant , round(@quant,3), convert(decimal(18,3),@quant)
Output:
37731.8 37731.801 37731.801
Great stuff dude.
You made a claim and gave a test case to support.
Nothing more I could ask for.