locked
Floating point problems RRS feed

  • Question

  • Hi! I am writing some functions in Server 2005 and i'm facing some weird problems, could somebody explain me whats happening ?? 

    I wrote the next code where i declare as float variable that should increase by .1 units in every cycle of the loop, and when I check the output notice that in the 60th cycle mi counter has increased .0001 : there is not so little numbers or a great number of cycles, why is this happening???

    Is there any solution ??? other than update my version because my hardware is limted

    Thanks in advance.

    /* declare @medhb float(15) set @medhb = 4.5 WHILE @medhb <= 18 BEGIN print STR(@medhb,10,5) set @medhb = @medhb + .1000 CONTINUE END */

    OUTPUT:

    10.30000
    10.40001
    10.50001
    10.60001


     
    Thursday, June 13, 2013 9:56 PM

Answers

  • No - this does not "solve" the problem.  If you select @medhb inside the loop, you will see that the actual value is very close but not exactly the desired value.  For example, 7.9 is actually 7.89999999999999.  Both you and OP have unintentionally hidden the approximate nature of floating point numbers by using STR function to display the value - which rounds while formatting (as we should expect). 

    The moral of the story is that you need to use the correct datatype and understand the limitations of your choice. If the requirement is exact precision, then the more appropriate datatype is probably decimal / numeric. 

    A more complete explanation of floating point usage in a computer in all its gory detail.

    Floating point arithmetic

    • Edited by scott_morris-ga Friday, June 14, 2013 7:04 PM added floating point link
    • Proposed as answer by Naomi N Monday, June 17, 2013 4:04 AM
    • Marked as answer by Allen Li - MSFT Saturday, June 22, 2013 3:09 AM
    Friday, June 14, 2013 6:59 PM
  • Hi,

    You shouldn't be using FLOATs for that kind of tasks.

    FLOATs are intended for scientific and statistical use, not storing counters, integers or currency amounts.

    For your particular case, NUMERIC or DECIMAL variables are best

    DECLARE @medhb NUMERIC(15,4)

    SET @medhb = 4.5

    ...


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Friday, June 14, 2013 7:07 PM
  • Use fixed decimal datatype instead of float.

    declare @medhb decimal(15,4)
    set @medhb = 4.5
    WHILE @medhb <= 18
    BEGIN
    print STR(@medhb,10,5)
    set @medhb = @medhb + .1000
    CONTINUE
    END

    Friday, June 14, 2013 7:21 PM

All replies

  • this happens regardless the value used as n in float(n) 
    Thursday, June 13, 2013 9:58 PM
  • Dear Avila,

    Based on BOL, Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.

    Syntax: float [ ( n ) ]

    --If n is specified, it must be a value between 1 and 53. The default value of n is 53.

    If you change value of n to 25 through 53, the precision will change to 15 digits and your problem will solve. Try this please:

    DECLARE @medhb FLOAT(25)
    SET @medhb = 4.5
    
    DECLARE @inc FLOAT(5)
    WHILE @medhb <= 18 
        BEGIN
    
            PRINT STR(@medhb, 10, 5)
    
            SET @medhb = @medhb + .1000
    
            CONTINUE
    
        END

    Regards,

    Saeid


    http://sqldevelop.wordpress.com/




    Thursday, June 13, 2013 10:31 PM
  • First of all we do not write loops in SQL; this is a declarative language. Next, you need to google how IEEE floating point works. This is part of freshman programming classes that deal with approximate numerics. Today, SQL programmers use DECIMAL(s,p); we are not limited to 16  bit hardware.


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, June 14, 2013 1:32 AM
  • No - this does not "solve" the problem.  If you select @medhb inside the loop, you will see that the actual value is very close but not exactly the desired value.  For example, 7.9 is actually 7.89999999999999.  Both you and OP have unintentionally hidden the approximate nature of floating point numbers by using STR function to display the value - which rounds while formatting (as we should expect). 

    The moral of the story is that you need to use the correct datatype and understand the limitations of your choice. If the requirement is exact precision, then the more appropriate datatype is probably decimal / numeric. 

    A more complete explanation of floating point usage in a computer in all its gory detail.

    Floating point arithmetic

    • Edited by scott_morris-ga Friday, June 14, 2013 7:04 PM added floating point link
    • Proposed as answer by Naomi N Monday, June 17, 2013 4:04 AM
    • Marked as answer by Allen Li - MSFT Saturday, June 22, 2013 3:09 AM
    Friday, June 14, 2013 6:59 PM
  • Hi,

    You shouldn't be using FLOATs for that kind of tasks.

    FLOATs are intended for scientific and statistical use, not storing counters, integers or currency amounts.

    For your particular case, NUMERIC or DECIMAL variables are best

    DECLARE @medhb NUMERIC(15,4)

    SET @medhb = 4.5

    ...


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Friday, June 14, 2013 7:07 PM
  • Use fixed decimal datatype instead of float.

    declare @medhb decimal(15,4)
    set @medhb = 4.5
    WHILE @medhb <= 18
    BEGIN
    print STR(@medhb,10,5)
    set @medhb = @medhb + .1000
    CONTINUE
    END

    Friday, June 14, 2013 7:21 PM