Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2003
    Posts
    33

    Unanswered: Why does decimal(38, 20) not give 20 decimal places?

    I've tried this on SQL Server 2000 and SQL Server 2005 and I can't seem to figure it out...

    If I declare a decimal(38, 20) it doesn't give me 20 decimal places.

    Code:
    declare @d1 decimal(38, 20)
    declare @d2 decimal(38, 20)
    
    set @d1 = 5000.00
    set @d2 = 13000.00
    
    select @d1 / @d2 -- RESULT: 0.384615
    However, if you don't specify ANY precision/scale then you get much higher precision/scale:

    Code:
    declare @d1 decimal
    declare @d2 decimal
    
    set @d1 = 5000.00
    set @d2 = 13000.00
    
    select @d1 / @d2 -- RESULT: 0.3846153846153846153
    However, if you divide 5000.0 / 13000.0 on a decent calculator you'll see the number goes out even further (eg. 0.38461538461538461538461538461538)

    What am I doing wrong and why does specifying the precision/scale actually reduce it?!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are only six digits, because that's what you've asked for... DECIMAL math works that way. The variables can store (and produce) more digits of precision if you let them.
    Code:
    DECLARE @d1		DECIMAL(38, 20)
    DECLARE @d2		DECIMAL(38, 20)
    
    SET @d1 = 5000.00
    SET @d2 = 13000.00
    
    SELECT @d1 /      @d2           -- RESULT: 0.384615
    SELECT @d1 / Cast(@d2 AS FLOAT) -- RESULT: 0.384615
    -PatP

  3. #3
    Join Date
    Oct 2003
    Posts
    33
    Huh? I'm a little confused...

    Aren't I asking for (38, 20) precision/scale?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You declared your variables so that they are capable of storing thirty-eight total digits of precision, scaled so that twenty of the thirty-eight are to the right of the decimal place. Note that this is an expresion of what the variables can store, this serves as the limit of what you can compute with these variables.

    You actually have two values, one of precision four and one of precision five. The result of multiplying these would have a potential precision of ten (one plus four plus five). The result of division, addition, and subtraction would have a potential precision of six (one plus five, the largest of the precisions of your original values). This is really fundamental algebra, it is implemented (correctly) by SQL Server, but it was defined by mathematicans centuries ago.

    -PatP

  5. #5
    Join Date
    Oct 2003
    Posts
    33
    Hmmm?

    Maybe I'm not understanding basic allgebra then (nor are several coworkers of mine).

    The precision and scale of the numbers being used are not affecting the result. The variable type is the only thing affecting the result. (eg. by using decimal(18,0) instead of decimal(38, 20) you get a MUCH higher resulting value even when you use the exact same numbers [5000.00 and 13000.00]).

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That is true, because the DECIMAL(18, 0) type can't represent the result of 5000 / 13000, the underflow forces the expression to take on a type of FLOAT instead of DECIMAL.

    -PatP

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Note the presence and abscence of the leading zero in the results. The abscence of the leading zero is the tell-tale for a FLOAT conversion. Note that in my example, there is a leading zero since I explicity forced the conversion of one argument to get more precision, but left the leading argument as a DECIMAL(38, 20) so it would determine the data type of the expression result.
    Code:
    DECLARE @d1		DECIMAL(38, 20)
    DECLARE @d2		DECIMAL(38, 20)
    
    DECLARE @d3		DECIMAL
    DECLARE @d4		DECIMAL
    
    DECLARE @d5		DECIMAL(18, 1)
    DECLARE @d6		DECIMAL(18, 1)
    
    SET @d1 = 5000.00
    SET @d2 = 13000.00
    
    SELECT @d1 /      @d2           -- RESULT: 0.384615
    SELECT @d1 / Cast(@d2 AS FLOAT) -- RESULT: 0.38461538461538464
    
    SET @d3 = @d1
    SET @d4 = @d2
    
    SELECT @d3 /      @d4           -- RESULT: .3846153846153846153
    
    SET @d5 = @d1
    SET @d6 = @d2
    
    SELECT @d5 /      @d6           -- RESULT: .38461538461538461538
    -PatP

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Professor Pat knows all.
    Professor Pat explains slowly.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    [note-to-self]I need a new professor[/note-to-self]
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    I recommend reading: What Every Computer Scientist Should Know About Floating Point html pdf

    (And, yes, I know we're dealing with fixed point, but the issues surrounding error, precision and scale are basically the same.)

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    Professor Pat explains slowly.
    What does a yellow light mean?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    green means go. they both start with G.
    yellow means yield. they both start with Y.
    red means stop. mmmmmmmmm.....
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Thrasymachus
    means stop. mmmmmmmmm.....

    Unless you're in Amsterdam
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Red definitely means stop (for hours sometimes) in Amsterdam.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •