Systems Engineering and RDBMS

Floating Point Arithmetic

Posted by decipherinfosys on April 29, 2009

This is nothing new but keeps coming up every now and then in some code snippet or a question by a reader.  Before we start, here is an excellent document on floating point arithmetic – “What every computer scientist should know about floating point arithmetic” and here is a wikipedia entry on floating point accuracy problems.

Floating point numbers give different results based on the order in which the arithmetic is done.  SQL Server does not have the same correction to this as the statistical products.  Here is what BOL has to say about float data types:

The float and real data types are known as approximate data types. The behavior of float and real follows the IEEE 754 specification on approximate numeric data types.

Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value. For many applications, the tiny difference between the specified value and the stored approximation is not noticeable. At times, though, the difference becomes noticeable. 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.

Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators. It is best to limit float and real columns to > or < comparisons.

The IEEE 754 specification provides four rounding modes: round to nearest, round up, round down, and round to zero. Microsoft SQL Server 2005 uses round up. All are accurate to the guaranteed precision but can result in slightly different floating-point values. Because the binary representation of a floating-point number may use one of many legal rounding schemes, it is impossible to reliably quantify a floating-point value.

Let’s first write up some examples of floating point arithmetic in SQL Server and then we can look into the explanations for the behavior:

/*Declare three local variables of data type float*/
declare @col1 float, @col2 float, @col3 float
/*Assign the values*/
select @col1 = 10, @col2 = 0.01
/*Assign the addition value to the third variable*/
select @col3 = @col1 + @col2
/*Now, Show the value in @col3 and the value for the subtraction*/
select @col3

———————-
10.01

/*This should show 0 but it won’t*/
select @col3 – @col1 – @col2

———————-
-2.13370987545147E-16

/*Now, reverse the values for the two variables*/
select @col1 = 0.01, @col2 = 10
select @col3 = @col1 + @col2
select @col3

———————-
10.01

/*This will show 0 now*/
select @col3 – @col1 – @col2
———————-
0

So, as you can see from above, the order in which the subtraction was done had an outcome on the final result.  We incurred a rounding error in the first case but not in the second one.  We have mostly used floats in projects/products which have to do with scientific calculation.  One needs to be aware of the “approximate” nature of the data type and the rounding issues that can arise when using the float data type.  Below are some very good links which have good posts and discussions on this topic.

Resources:

  • The links given in the post above.
  • Post by MVP Hugo Kornelis – here and another post (check the comments as well) by MVP Michael Coles – here.
  • A function by MVP Erland Sommarskog for rounding the float values – here.

5 Responses to “Floating Point Arithmetic”

  1. Mark Roddy said

    “Floating point numbers give different results based on the order in which the arithmetic is done. ”

    Does this also mean that when using an Aggregate Function on a float column the ordering of the values affects the final result?

  2. Jethro said

    Hi, please check the following:

    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

    I think the main differnce is that Float use ‘Binary System’ but Decimal use ‘Decimal System’. So in theory, Decimal is more nature and better than Float. But in fact, it is difficult to choose — There should has an stardand to resolve the problem.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: