January 14, 2005 at 7:16 am
Hi,
I'm using the following system:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
I'm trying to do some pretty basic arithmetic, but am having problems with decimal numbers being rounded. All my variables are decimal(28,15).
The following script demonstrates the problem. The equation should (surely?!) give @number2 as the answer.
As part of my experimenting I introduced a CAST, which seemed to solve the problem, but it only works for certain sets of numbers! The first script demonstrates how the cast seems to fix the problem, but in the second set the cast creates it's own problem.
-- version 1 - correct answer with cast
begin
declare @answer1 decimal(28,15)
declare @answer2 decimal(28,15)
declare @Number1 decimal(28,15)
declare @Number2 decimal(28,15)
set @Number1 = 108.048305000000000
set @Number2 = 109.096373558500000
set @answer1 = @Number1 * (@Number2/@Number1)
set @answer2 = @Number1 * cast((@Number2/@Number1) as decimal(28,15))
print @answer1
print @answer2
end
go
-- version 2 - correct answer without cast
begin
declare @answer1 decimal(28,15)
declare @answer2 decimal(28,15)
declare @Number1 decimal(28,15)
declare @Number2 decimal(28,15)
set @Number1 = 99.150000000000000
set @Number2 = 90.454500000000000
set @answer1 = @Number1 * (@Number2/@Number1)
set @answer2 = @Number1 * cast((@Number2/@Number1) as decimal(28,15))
print @answer1
print @answer2
end
January 14, 2005 at 7:35 am
Well,
You've got to expect some arithmetic roundoff to occur. Numbers you're getting don't seem that unreasonable. That's the way the numerical cookie crumbles.
One suggestion, though. To reduce the effects of roundoff, do all numeric calculations in a native floating point format (e.g., FLOAT), and hold off on the cast to DECIMAL (for formatting) until the last possible instant. I haven't looked to see how many bytes SQL uses to store DECIMAL numbers internally, but my gut intuition says that is the best approach, regardless:
-- version 3 - With FLOATs
begin
declare @Number1 FLOAT
declare @Number2 FLOAT
declare @answer1 FLOAT
set @Number1 = 99.150000000000000
set @Number2 = 90.454500000000000
set @answer1 = @Number1 * (@Number2/@Number1)
print @Number1 -- 99.15
print @Number2 -- 90.4545
print CAST(@answer1 AS decimal(28,15)) -- 90.454499999999996 -- Some (Reasonable) Roundoff!
print CAST(@answer1 AS decimal(28,4)) -- 90.4545 -- With Formatting, It Looks Right On the Money!
end
Regards!
- john
January 14, 2005 at 7:46 am
You might want to have a look at "Precision, Scale, and Length" in BOL. It contains a nice table:
| Operation | Result precision | Result scale * |
|---|---|---|
| e1 + e2 | max(s1, s2) + max(p1-s1, p2-s2) + 1 | max(s1, s2) |
| e1 - e2 | max(s1, s2) + max(p1-s1, p2-s2) | max(s1, s2) |
| e1 * e2 | p1 + p2 + 1 | s1 + s2 |
| e1 / e2 | p1 - s1 + s2 + max(6, s1 + p2 + 1) | max(6, s1 + p2 + 1) |
* 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.
SQL Server has a limited number of bytes (max. 17) to use for these values and as its algorithms won't risk to lose a significant digit, it must reduces the scale.
Or put it simple: You should have better luck if you don't declare your data with such an extreme precision
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
January 15, 2005 at 2:57 am
I ended up running my script with decimal(21,15) types, and the inaccuracies were greatly reduced. Using 20,15 seemed even better, but unfortunately some numbers were over 100,000, so I needed 6 digits on the left of the decimal point.
One strange thing I discovered was that reducing the scale (the 15 part) made the problem worse, even though the numbers I was processing generally only had 5 or 10 significant figures to the right of the decimal point.
There is some related info in the following discussion, based on the same question:
Sauce.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply