Hi there,
we are experiencing some very unexptected behaviour here (SQL 2k, latest
SPs, different types of hardware).
Since our application is doing approximations we are trying to get as much
detail as possible from the calculations. However, instead of getting better
results with Numeric(38,25) variables, we are running into Arithmetic
Overflows!!?
As one query can say so much more than a thousand words : please try this :
DECLARE @.x numeric(25, 15)
DECLARE @.y numeric(25, 15)
SELECT @.x = 10000000, -- 10 Million
@.y = 1.021208219120586
SELECT x = @.x, y = @.y
SELECT result = @.x / @.y
SELECT inverse = @.y / @.x
SELECT result_via_inv = Convert(Numeric(25, 15), 1) / (@.y / @.x)
As you will see, this gives me some numbers.
Now replace the Numeric() definitions to something more precise (eg. 38, 25)
Can anyobdoy explain this ? I've found that using 25,15 sometimes gives me
overflows too due to the fact that the amounts involved sometimes go over
10^10. Should I use floats and live with the 'loss of precision' ?
Thanks.
Cu
RobySee "Precision, Scale, and Length" in BOL.
> SELECT result = @.x / @.y
The p ans s of the result will be (38, 38), equal p and s. Whatever number
that you try to cast to numeric(p, s) where p = s, will give you an error
except for zero.
-- error
select cast(1 numeric(38, 38))
-- error
select cast(1 numeric(12, 12))
-- no error
select cast(1 numeric(38, 38))
> SELECT inverse = @.y / @.x
The result is zero and zero can be cast to numeric(38, 38)
> SELECT result_via_inv = Convert(Numeric(25, 15), 1) / (@.y / @.x)
Because @.y / @.x is zero, then you have "Divide by zero error encountered."
error.
May be I am wrong here, so I will call for help from Steve Kass.
AMB
"deroby" wrote:
> Hi there,
> we are experiencing some very unexptected behaviour here (SQL 2k, latest
> SPs, different types of hardware).
> Since our application is doing approximations we are trying to get as much
> detail as possible from the calculations. However, instead of getting bett
er
> results with Numeric(38,25) variables, we are running into Arithmetic
> Overflows!!?
> As one query can say so much more than a thousand words : please try this
:
>
> DECLARE @.x numeric(25, 15)
> DECLARE @.y numeric(25, 15)
> SELECT @.x = 10000000, -- 10 Million
> @.y = 1.021208219120586
> SELECT x = @.x, y = @.y
> SELECT result = @.x / @.y
> SELECT inverse = @.y / @.x
> SELECT result_via_inv = Convert(Numeric(25, 15), 1) / (@.y / @.x)
> As you will see, this gives me some numbers.
> Now replace the Numeric() definitions to something more precise (eg. 38, 2
5)
> Can anyobdoy explain this ? I've found that using 25,15 sometimes gives me
> overflows too due to the fact that the amounts involved sometimes go over
> 10^10. Should I use floats and live with the 'loss of precision' ?
> Thanks.
> Cu
> Roby|||Correction,
-- error
select cast(1 numeric(38, 38))
-- error
select cast(1 numeric(12, 12))
-- no error
select cast(0 numeric(38, 38))
AMB
"Alejandro Mesa" wrote:
> See "Precision, Scale, and Length" in BOL.
>
> The p ans s of the result will be (38, 38), equal p and s. Whatever number
> that you try to cast to numeric(p, s) where p = s, will give you an error
> except for zero.
> -- error
> select cast(1 numeric(38, 38))
> -- error
> select cast(1 numeric(12, 12))
> -- no error
> select cast(1 numeric(38, 38))
>
> The result is zero and zero can be cast to numeric(38, 38)
>
> Because @.y / @.x is zero, then you have "Divide by zero error encountered."
> error.
>
> May be I am wrong here, so I will call for help from Steve Kass.
>
> AMB
> "deroby" wrote:
>|||Interesting, another one for the favorites !
So in order to get this working I should make sure the resuling p & s are
what they are to be in the result column.
I should be able to figure that out =)
Thanks !
Cu
Roby
"Alejandro Mesa" wrote:
> Correction,
> -- error
> select cast(1 numeric(38, 38))
> -- error
> select cast(1 numeric(12, 12))
> -- no error
> select cast(0 numeric(38, 38))
>
> AMB
> "Alejandro Mesa" wrote:
>|||I think that my comment was wrong. I know that the result of @.x / @.y can not
be represented in the result precision and scale, but I do not know those
values. If you reduce the scale or cast one of the variables to float, then
you do not get the error.
AMB
"deroby" wrote:
> Interesting, another one for the favorites !
> So in order to get this working I should make sure the resuling p & s are
> what they are to be in the result column.
> I should be able to figure that out =)
> Thanks !
> Cu
> Roby
> "Alejandro Mesa" wrote:
>|||Hi Alejandro,
Here is my best shot at explaining what deroby sees here:
There are 772 decimal types in SQL Server, and the typing
of expressions involving decimals is tricky business!
The topic "Precision, Scale, and Length" describes
the rules for typing arithmetic on decimal types.
The type of <decimal value 1> <operator> <decimal value 2>
is based on the types, not the values, in the expressions.
Since the result of d1/d2 can be much larger than d1 or
require more places after the decimal point than d1, an
attempt is made to choose a result type that avoids both
overflow and inaccuracy. This is not always possible.
For small cases, there is little problem. The result
of numeric(8,4)/numeric(8,4) could range from around
0.000000000001 to 100000000000.0, and the result type
for this expression is numeric(21,13), which avoids
overflow and maximizes correctness, at least by avoiding
truncation to zero. (Note that no decimal type can exactly
represent 1.0/3.0, so we can't have complete accuracy.)
But for something like numeric(38,15)/numeric(38,15), it
is nearly impossible to account for all possibilities.
The result could range in size from around 10^-15/10^23 to 10^23/10^-15,
which is about 76 orders of magnitude. SQL Server tries harder
to avoid overflow than to be accurate (perhaps since overflow causes
a run-time error, but loss of accuracy doesn't). There is only
a "safety" of never reducing the scale below 6 if it
should really be larger. It turns out the result type for
this quotient in this case is numeric(38,6). This type cannot
represent the reciprocal of 10000000, so it is rounded to zero.
You can see what the result precisions and scales are by
converting to varbinary and inspecting the first two bytes
of the result:
DECLARE @.x numeric(25, 15)
DECLARE @.y numeric(25, 15)
SELECT @.x = 10000000 -- 10 Million
SELECT @.y = 1
SELECT
@.y / @.x as quotient,
cast(@.y / @.x as varbinary) as binRep,
cast(substring(cast(@.y / @.x as varbinary),1,1) as tinyint) as prec,
cast(substring(cast(@.y / @.x as varbinary),2,1) as tinyint) as scale
go
DECLARE @.x numeric(38, 15)
DECLARE @.y numeric(38, 15)
SELECT @.x = 10000000 -- 10 Million
SELECT @.y = 1
SELECT
@.y / @.x as quotient,
cast(@.y / @.x as varbinary) as binRep,
cast(substring(cast(@.y / @.x as varbinary),1,1) as tinyint) as prec,
cast(substring(cast(@.y / @.x as varbinary),2,1) as tinyint) as scale
Steve Kass
Drew University
Alejandro Mesa wrote:
>See "Precision, Scale, and Length" in BOL.
>
>
>The p ans s of the result will be (38, 38), equal p and s. Whatever number
>that you try to cast to numeric(p, s) where p = s, will give you an error
>except for zero.
>-- error
>select cast(1 numeric(38, 38))
>-- error
>select cast(1 numeric(12, 12))
>-- no error
>select cast(1 numeric(38, 38))
>
>
>The result is zero and zero can be cast to numeric(38, 38)
>
>
>Because @.y / @.x is zero, then you have "Divide by zero error encountered."
>error.
>
>May be I am wrong here, so I will call for help from Steve Kass.
>
>AMB
>"deroby" wrote:
>
>|||Steve,
Thanks a lot for the explanation and the tip.
Alejandro Mesa
"Steve Kass" wrote:
> Hi Alejandro,
> Here is my best shot at explaining what deroby sees here:
> There are 772 decimal types in SQL Server, and the typing
> of expressions involving decimals is tricky business!
> The topic "Precision, Scale, and Length" describes
> the rules for typing arithmetic on decimal types.
> The type of <decimal value 1> <operator> <decimal value 2>
> is based on the types, not the values, in the expressions.
> Since the result of d1/d2 can be much larger than d1 or
> require more places after the decimal point than d1, an
> attempt is made to choose a result type that avoids both
> overflow and inaccuracy. This is not always possible.
> For small cases, there is little problem. The result
> of numeric(8,4)/numeric(8,4) could range from around
> 0.000000000001 to 100000000000.0, and the result type
> for this expression is numeric(21,13), which avoids
> overflow and maximizes correctness, at least by avoiding
> truncation to zero. (Note that no decimal type can exactly
> represent 1.0/3.0, so we can't have complete accuracy.)
> But for something like numeric(38,15)/numeric(38,15), it
> is nearly impossible to account for all possibilities.
> The result could range in size from around 10^-15/10^23 to 10^23/10^-15,
> which is about 76 orders of magnitude. SQL Server tries harder
> to avoid overflow than to be accurate (perhaps since overflow causes
> a run-time error, but loss of accuracy doesn't). There is only
> a "safety" of never reducing the scale below 6 if it
> should really be larger. It turns out the result type for
> this quotient in this case is numeric(38,6). This type cannot
> represent the reciprocal of 10000000, so it is rounded to zero.
> You can see what the result precisions and scales are by
> converting to varbinary and inspecting the first two bytes
> of the result:
> DECLARE @.x numeric(25, 15)
> DECLARE @.y numeric(25, 15)
> SELECT @.x = 10000000 -- 10 Million
> SELECT @.y = 1
> SELECT
> @.y / @.x as quotient,
> cast(@.y / @.x as varbinary) as binRep,
> cast(substring(cast(@.y / @.x as varbinary),1,1) as tinyint) as prec,
> cast(substring(cast(@.y / @.x as varbinary),2,1) as tinyint) as scale
> go
> DECLARE @.x numeric(38, 15)
> DECLARE @.y numeric(38, 15)
> SELECT @.x = 10000000 -- 10 Million
> SELECT @.y = 1
> SELECT
> @.y / @.x as quotient,
> cast(@.y / @.x as varbinary) as binRep,
> cast(substring(cast(@.y / @.x as varbinary),1,1) as tinyint) as prec,
> cast(substring(cast(@.y / @.x as varbinary),2,1) as tinyint) as scale
>
> Steve Kass
> Drew University
> Alejandro Mesa wrote:
>
>
