none
Stop SQL server from rounding precise calculations

    Question

  • Hi,

    I'm trying to caluclate some very extact figures using a fairly complicated algorithm and I'm getting wild variances in the output. I've tracked it down and it would seem SQL server rounds when ever it feels like it.

    Here is a simplified version of my problem...

    DECLARE

    @A DECIMAL(38,17), @B DECIMAL(38,17)

    SELECT

    @A = 55.5147203733824, @B = 53.2679199504953

    SELECT

    A, B, A - B AS c

    , (A-B)/ B AS PC

     

    FROM

     

    (SELECT @A AS A, @B AS B

    )

    x

    As you can see PC has been round down considerably. If I explicitly tell SQL server to use more memory for the calc I still don't get the precision I've requested.

    eg.

    ,(CAST( A AS DECIMAL(38,17)) - CAST( B AS DECIMAL(38,17))) / CAST( B AS DECIMAL(38,17)) AS PC_CAST

    Casting the input has no effect at all. 

    Thanks,

    Gary

    Tuesday, July 06, 2010 11:34 AM

Answers

  • I've tracked it down and it would seem SQL server rounds when ever it feels like it.

    Here is a simplified version of my problem...

    DECLARE

    @A DECIMAL(38,17), @B DECIMAL(38,17)

    You are pushing SQL Server to the limit, and forcing it to round.

    If you multiply two numbers of 2 digits, then the end result could take up to 4 digits. If you multiply two number of 3 digits, the end result could take up to 6 digits.

    Similar rules apply when you divide number, etc.

    The maximum precision of decimal is 38 digits, and you have declared the variables as such. This means that all room to "grow" the end result in precision is made impossible. If I change the definition of @A and @B to DECIMAL(15,13) then I get this result:

    A                 B                 c                  PC                                       

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

    55.5147203733824  53.2679199504953  2.2468004228871    .0421792408071344002616

    Are those the results you were looking for?

    Look up the topic "Precision, Scale, and Length (Transact-SQL)" in BOL. It will tell you exactly how precision and scale determine the maximum precision and scale after performing mathematical operations.

    -- 

    Gert-Jan

     

    • Marked as answer by KJian_ Monday, July 12, 2010 6:03 AM
    Tuesday, July 06, 2010 4:57 PM

All replies

  • What should a desired value, can you expand a little bit?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, July 06, 2010 11:39 AM
    Answerer
  • What should a desired value, can you expand a little bit?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, July 06, 2010 11:39 AM
    Answerer
  • I've tracked it down and it would seem SQL server rounds when ever it feels like it.

    Here is a simplified version of my problem...

    DECLARE

    @A DECIMAL(38,17), @B DECIMAL(38,17)

    You are pushing SQL Server to the limit, and forcing it to round.

    If you multiply two numbers of 2 digits, then the end result could take up to 4 digits. If you multiply two number of 3 digits, the end result could take up to 6 digits.

    Similar rules apply when you divide number, etc.

    The maximum precision of decimal is 38 digits, and you have declared the variables as such. This means that all room to "grow" the end result in precision is made impossible. If I change the definition of @A and @B to DECIMAL(15,13) then I get this result:

    A                 B                 c                  PC                                       

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

    55.5147203733824  53.2679199504953  2.2468004228871    .0421792408071344002616

    Are those the results you were looking for?

    Look up the topic "Precision, Scale, and Length (Transact-SQL)" in BOL. It will tell you exactly how precision and scale determine the maximum precision and scale after performing mathematical operations.

    -- 

    Gert-Jan

     

    • Marked as answer by KJian_ Monday, July 12, 2010 6:03 AM
    Tuesday, July 06, 2010 4:57 PM