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

1. Registered User
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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. Registered User
Join Date
Oct 2003
Posts
33
Huh? I'm a little confused...

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

4. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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. Registered User
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Professor Pat knows all.
Professor Pat explains slowly.

9. Registered User
Join Date
Jul 2003
Location
San Antonio, TX
Posts
3,662
[note-to-self]I need a new professor[/note-to-self]

10. Registered User
Join Date
Oct 2002
Location
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. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Originally Posted by blindman
Professor Pat explains slowly.
What does a yellow light mean?

12. Annie's Dog Walker
Join Date
Nov 2004
Location
on the wrong server
Posts
8,842
red means stop. mmmmmmmmm.....

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

Unless you're in Amsterdam

14. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579