Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Issue with "decimal" type and division

Answered Issue with "decimal" type and division

  • Thursday, January 31, 2013 5:18 AM
     
     
    Sign In to Vote
    0

    Using Excel

            123.25 /37000000 = 0.00000333108108108108

    But the same division, shown below,  returns 0.000003. Why the loss in precision?

    (The numbers involved are declared as decimal (38,20) where the scale (20) indicates the

    maximum number of decimals to the right of the decimal point.)

            declare @val1 decimal (38,20)
            declare @val2 decimal (38,20)

            select @val1 = 123.25
            select @val2 = 37000000.00

            select @val1/ @val2       -- returns  0.000003 !

    TIA,

    edm2

    P.S. I changed all the decimal(38,20) types to "real" and the answer returned was "3.331081E-06" which is a lot more accurate than what decimal returns.

     P.P.S. I am looking for Excel accuracy from my t-sql statements.



    • Edited by edm2 Thursday, January 31, 2013 5:25 AM
    •  
     

All Replies

  • Thursday, January 31, 2013 5:32 AM
     
      Has Code
    Sign In to Vote
    0

    Try the below:

    declare @val1 decimal (38,20)
        declare @val2 decimal (38,20)
        select @val1 = 123.25
        select @val2 = 37000000.00
        select cast(Cast(@val1 as float)/ Cast(@val2  as float) as decimal (38,20))     -- returns  0.000003 !

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

     
  • Thursday, January 31, 2013 5:44 AM
     
     
    Sign In to Vote
    0

    Latheesh --

    I was just going to update my original entry. I discovered, as you show above, that by changing all my decimal(38,20) entries to float that the mathematics become exact. By exact I mean that I get

        select (@val1/@val2) =  3.33108108108108E-06

    and

        select (@val1/ @val2)   * @val2  = 123.25      -- no roundoff error at all.

    The question remains though: why does using decimal (38,20) introduce so much roundoff error?

    >>>>>>>>>>>>>

    Discovery: t-sql decimals has subtleties I don't understand

    -- produces "Arithmetic overflow error converting numeric to data type numeric."
    declare @val2 decimal (18,15)
    select @val2 = cast (37000000.00 as decimal (18,15))

    -- produces "Command(s) completed successfully."
    declare @val2 decimal (38,15)
    select @val2 = cast (37000000.00 as decimal (38,15))

    The "15" is not a mandatory number of digits but the max requested. Why the first query (overflow) fails is beyond me.

    >>>>>>>>>>>>>

    edm2


    • Edited by edm2 Thursday, January 31, 2013 6:16 AM
    •  
     
  • Thursday, January 31, 2013 6:16 AM
     
     
    Sign In to Vote
    0

    Using Excel

            123.25 /37000000 = 0.00000333108108108108

    But the same division, shown below,  returns 0.000003. Why the loss in precision?

    (The numbers involved are declared as decimal (38,20) where the scale (20) indicates the

    maximum number of decimals to the right of the decimal point.)

            declare @val1 decimal (38,20)
            declare @val2 decimal (38,20)

            select @val1 = 123.25
            select @val2 = 37000000.00

            select @val1/ @val2       -- returns  0.000003 !

    TIA,

    edm2

    P.S. I changed all the decimal(38,20) types to "real" and the answer returned was "3.331081E-06" which is a lot more accurate than what decimal returns.

     P.P.S. I am looking for Excel accuracy from my t-sql statements.



    Try this

            declare @val1 float
            declare @val2 float

            select @val1 = 123.25
            select @val2 = 37000000.00

            select @val1/ @val2   

    Many Thanks & Best Regards, Hua Min


     
  • Thursday, January 31, 2013 6:23 AM
     
     
    Sign In to Vote
    0

    I think you will get the truncation answer in the following link : http://msdn.microsoft.com/en-us/library/ms190476(v=sql.105).aspx

    And I think floating point number gives you a higher range of values to store

    Thanks and regards, Rishabh K

     
  • Thursday, January 31, 2013 6:38 AM
     
     Answered Has Code
    Sign In to Vote
    0

     declare @val1 decimal (38,20)
            declare @val2 decimal (38,20)

            select @val1 = 123.25
            select @val2 = 37000000.00

            select @val1/ @val2       -- returns  0.000003 !

    There are some rules when you do the arithmatic operatios on decimal expressions for deciding the precision and scale of the result as described in the below article from Books Online:

    Precision, Scale, and Length (Transact-SQL)

    According to the rule for division (e1 / e2) the formula for precision is:

    p1 - s1 + s2 + max(6, s1 + p2 + 1) - In your case this will be 38 - 20 + 20 + MAX(6, 20 + 38 + 1) = 97

    Formula for scale is : max(6, s1 + p2 + 1) - In your case this will be MAX(6, 20 + 38 + 1) = 59. According to the same article:

    The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated

    However how much this will be truncated....? The article doesn't provide any info on this. According to the SQL Programability team blog below :

    Multiplication and Division with Numerics

    the resultant scale will be minimum of 6. That is what you are getting when dividing @val1 and @val2.

    In order to get the desired output you can just change the precision and scale as below:

    declare @val1 decimal (18,10)
declare @val2 decimal (18,10)
select @val1 = 123.25
select @val2 = 37000000.00
select @val1/ @val2 -- 0.00000333108108108108
    Hope this is clear.

    Krishnakumar S

     
  • Thursday, January 31, 2013 7:04 AM
     
     
    Sign In to Vote
    0

    Krishnakumar,

    Excellent answer and I believe it describes the heart of the problem. The Decimal type, in practice, has subtleties that make it less than useful for extensive calculations. Float is better. Look at these simple examples.


    -- gives 123.5, rounded up.
    select CAST( 123.45 as decimal(5,1))

    -- gives 123.45. That makes sense. Two digits to the right of the decimal fits in a (5,2)
    select CAST( 123.45 as decimal(5,2))

    -- gives "Arithmetic overflow error converting numeric to data type numeric."

    select CAST( 123.45 as decimal(5,3))

    Surprising as "123.45" should fit in a (5.3). On the other hand Decimal is always advertised as an "exact" type and one could obscurely remark that 2 digits to the right of the decimal point do not exactly fit a (5,3) format. "123.450" would fit that format. Oops! Make that Decimal (6,3) now! Things are getting trickier!


    Per Excel: 123.45 * 123.45 = 15239.9025
    t-sql    : 15239.9025.  As 123.45 has two decimals the square of the number contains (2+2) = 4 decimals. This calculation works fine.
         select CAST( 123.45 as decimal(5,2)) * CAST( 123.45 as decimal(5,2))

    -- Surprise:  The result of the statement below is "Arithmetic overflow error converting numeric to data type numeric."

    select CAST ( 123.45 * 123.45 as decimal(5,4))

    because the actual result is  15239.9025 which will not fit in a decimal(5,4) but will fit in a decimal(9,4).  (Basically one needs to add the components (5,4) twice because we are multiplying two (5,2) numbers).

    My point: decimals are an exact type. Unfortunately when dealing with intricate calculations (over thousand of rows) it is usually not possible to know ahead of time the size of the result, either precision or scale.  Therefore using Decimal with predefined (x,y) components as the data type in a computation will likely lead to an Overflow or possible truncation. Decimals are good for exact storage but not where the result characteristics are unknown (which is usually the case). For that float -- with its wider range of numeric representation -- is more appropriate. Float can also suffer from rounding and truncation but its results seems to be more in line with what one "expects" than the unknown\ unpredictable result of using Decimal types and expecting exact answers.

    edm2



    • Edited by edm2 Thursday, January 31, 2013 7:11 AM
    •  
     
  • Thursday, January 31, 2013 7:20 AM
     
     
    Sign In to Vote
    0

    I think you are confused with the usage of precision and scale...Have a look at the below thread with a similar issue/description:

    Why an SQL Server db field [DataType is:Decimal(5,4)] cannot accept number like (12.333)

    Hope this will make things clear.

    Krishnakumar S

     
  • Thursday, January 31, 2013 7:41 AM
    Moderator
     
     
    Sign In to Vote
    0

    >The Decimal type, in practice, hassubtleties that make it less than useful for extensive calculations. Float is better.

    For business calculations MONEY and DECIMAL are the best.

    FLOAT/REAL is better for scientific/engineering applications.

    Kalman Toth SQL 2008 GRAND SLAM
    Paperback: SQL Server 2012 Pro

     
  • Thursday, January 31, 2013 2:11 PM
     
     
    Sign In to Vote
    0

    Kalman,

    Regarding >>> For business calculations MONEY and DECIMAL are the best.

    That may be true in some cases but not in all. In fact, the original issue that prompted my question was a business computation involving only "+" ,"\" , and "*" extended over a large number (100,000+) of customers. The numbers involved, unfortunately, ranged from small to large and using Decimal fields in the computation led to either roundoff or truncation error. The error was on the order of 5% but that is not acceptable, given that Excel, out of the box, had an error of 0%.

    Still, I think in simple cases (e.g. normal day-to-day, one on one,  customer-based transactions) that  "Money\Decimal" are fine but not the best choice when dealing with computations over a large number of customers. 

    edm2


    • Edited by edm2 Thursday, January 31, 2013 3:59 PM
    •  
     
  • Thursday, January 31, 2013 9:02 PM
     
     Answered
    Sign In to Vote
    1

    If float works for you, then that's great.

    And while there is a lot of truth to your rant, such as the intricacies that accompany decimals,  I don't agree with your statement that it is "usually not possible to know ahead of time the size of the result, either precision or scale". Because tell me, what real life object needs a precision of 38 digits? Seriously!  Money amounts don't, counts don't, measuring values highly unlikely...

    From the formula that Krishnakumar S posted, you can see that if the precision of your values is a massive 18 digits, then there is no loss of precision for any multiplication or division. Does any of the columns in your database have values that need anything close to that precision?

    I think that most of the time, the proper precision (and scale) is not chosen, and someone just selects the lazy option of the biggest possible declaration. When that happens, yes, then you run into problems with decimal's intricacies.

    Gert-Jan

     
  • Thursday, January 31, 2013 9:32 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Actually I would argue Excel is wrong.  The true mathmatical result due to "significant digits" is 0.000003.  The rest are "insignificant digits".  This is a good explananation of significant digits. http://scienceblogs.com/goodmath/2009/03/04/basics-significant-figures/

    As others have pointed out, if you use float, you will get a huge nonsense number.  This is partly because float is an approximation, which matches Excel because it uses float for all numeric calculations.