URGENT!! Decimal data type query

Atenza

declare @amount1 decimal(28,10)
declare @amount2 decimal(28,10)
set @amount1 = 0.0275
set @amount2 = 0.0105
select @amount1 * @amount2

i expect the result = 0.00028875, however, it only return 0.000289 why??
the declartion of the decimal data type specify the decimal place up to 10,
how come after mulitple, only 6 decimal places return? please help me!

Thanks in advance!
Martin

Ben Nevarez

Hi Martin,

Probably the explanation is here

Precision, Scale, and Length (Transact-SQL)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/fbc9ad2c-0d3b-4e98-8fdd-4d912328e40a.htm

For the operation e1 * e2, with precision p1 and scale s1, and precision p2
and scale s2, the precision for the result will be p1 + p2 + 1 and the scale
for the result will be s1 + s2. But there is a note saying that if a
precision is greater than 38, the scale is "reduced to prevent the integral
part of a result from being truncated".

For example if you use

declare @amount1 decimal(18,10)
declare @amount2 decimal(18,10)
set @amount1 = 0.0275
set @amount2 = 0.0105
select @amount1 * @amount2

you get

0.00028875000000000000

where the scale is s1 + s2 = 10 + 10 = 20, and the precision is less than 38.

Hope this helps,

Ben Nevarez
Senior Database Administrator
AIG SunAmerica



"Atenza" wrote:

> declare @amount1 decimal(28,10)
> declare @amount2 decimal(28,10)
> set @amount1 = 0.0275
> set @amount2 = 0.0105
> select @amount1 * @amount2
>
> i expect the result = 0.00028875, however, it only return 0.000289 why??
> the declartion of the decimal data type specify the decimal place up to 10,
> how come after mulitple, only 6 decimal places return? please help me!
>
> Thanks in advance!
> Martin
>
>
>

Pawel Potasinski

If you look into BOL ("Precision, Scale, and Length (Transact-SQL)" chapter)
then you will find some table containing precision and scale of decimal data
after arithmetic operations.

And if you multiply two decimals: on of precision p1 and scale s1 and the
second of precision p2 and scale s2 you gain precision (p3) and scale (s3)
as shown below.

p3 = p1 + p2 + 1 -> p3 = 28 + 28 + 1 = 57 ???
s3 = s1 + s2 -> s3 = 10 + 10 = 20

As you may know the maximum precision of decimal is 38. If the calculated
precision of the result reaches 37 or more then the scale is reduced to
avoid theoretical truncation of meaningful data. However, the minimum scale
reached with this process is 6. And that's your situation.

Try a little test:

declare @amount1 decimal(28,10)
declare @amount2 decimal(28,10)
set @amount1 = 0.0275
set @amount2 = 0.0105
SELECT @amount1, @amount2
SELECT
SQL_VARIANT_PROPERTY(CAST(@amount1 * @amount2 AS
SQL_VARIANT),'precision'), -- will be 38
SQL_VARIANT_PROPERTY(CAST(@amount1 * @amount2 AS
SQL_VARIANT),'scale') -- will be 6 (!)

And another one:

declare @amount1 decimal(28,10)
declare @amount2 decimal(18,10) -- changed precision to 18, so the
calculated precision of the result is 46
set @amount1 = 0.0275
set @amount2 = 0.0105
SELECT @amount1, @amount2
SELECT
SQL_VARIANT_PROPERTY(CAST(@amount1 * @amount2 AS
SQL_VARIANT),'precision'), -- will be 38 (as this is the maximum precision
available)
SQL_VARIANT_PROPERTY(CAST(@amount1 * @amount2 AS
SQL_VARIANT),'scale') -- will be 11 (because 11 = s1 + s2 - [p1 + p2 - 37] =
10 + 10 - [28+18-37])

IMHO, this is not the worst part of numeric operations. Understanding of
precision and scale after division is the real challenge :-)

--
Regards
Pawel Potasinski
[


U¿ytkownik "Atenza" <> napisa³ w wiadomo¶ci
news:.gbl...
> declare @amount1 decimal(28,10)
> declare @amount2 decimal(28,10)
> set @amount1 = 0.0275
> set @amount2 = 0.0105
> select @amount1 * @amount2
>
> i expect the result = 0.00028875, however, it only return 0.000289 why??
> the declartion of the decimal data type specify the decimal place up to
> 10, how come after mulitple, only 6 decimal places return? please help me!
>
> Thanks in advance!
> Martin
>

Atenza

Thank you very much!!
It seems that there is no easy way to solve in my situation.

"Ben Nevarez" <> wrote in message
news:...
>
> Hi Martin,
>
> Probably the explanation is here
>
> Precision, Scale, and Length (Transact-SQL)
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/fbc9ad2c-0d3b-4e98-8fdd-4d912328e40a.htm
>
> For the operation e1 * e2, with precision p1 and scale s1, and precision
> p2
> and scale s2, the precision for the result will be p1 + p2 + 1 and the
> scale
> for the result will be s1 + s2. But there is a note saying that if a
> precision is greater than 38, the scale is "reduced to prevent the
> integral
> part of a result from being truncated".
>
> For example if you use
>
> declare @amount1 decimal(18,10)
> declare @amount2 decimal(18,10)
> set @amount1 = 0.0275
> set @amount2 = 0.0105
> select @amount1 * @amount2
>
> you get
>
> 0.00028875000000000000
>
> where the scale is s1 + s2 = 10 + 10 = 20, and the precision is less than
> 38.
>
> Hope this helps,
>
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
>
>
> "Atenza" wrote:
>
>> declare @amount1 decimal(28,10)
>> declare @amount2 decimal(28,10)
>> set @amount1 = 0.0275
>> set @amount2 = 0.0105
>> select @amount1 * @amount2
>>
>> i expect the result = 0.00028875, however, it only return 0.000289 why??
>> the declartion of the decimal data type specify the decimal place up to
>> 10,
>> how come after mulitple, only 6 decimal places return? please help me!
>>
>> Thanks in advance!
>> Martin
>>
>>
>>

Atenza

SQL_VARIANT_PROPERTY(CAST(@amount1 * @amount2 AS SQL_VARIANT),'scale')
-- will be 11 (because 11 = s1 + s2 - [p1 + p2 - 37] = 10 + 10 - [28+18-37])

How do you know the scale is s1 + s2 - [p1 + p2 - 37]?
From the BOL ("Precision, Scale, and Length (Transact-SQL)" chapter), only
s1 + s2 will be the result scale after e1 * e2
Except 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.

this is not the worst part of numeric operations <-- It seems that you have
encountered worst numeric operation that this, i am quite interesting, would
you mind to share?

Anyway, thank your for your kind explanation.
Martin ^_^

"Pawel Potasinski" <> wrote in message
news:e7ozG$.gbl...
> If you look into BOL ("Precision, Scale, and Length (Transact-SQL)"
> chapter) then you will find some table containing precision and scale of
> decimal data after arithmetic operations.
>
> And if you multiply two decimals: on of precision p1 and scale s1 and the
> second of precision p2 and scale s2 you gain precision (p3) and scale (s3)
> as shown below.
>
> p3 = p1 + p2 + 1 -> p3 = 28 + 28 + 1 = 57 ???
> s3 = s1 + s2 -> s3 = 10 + 10 = 20
>
> As you may know the maximum precision of decimal is 38. If the calculated
> precision of the result reaches 37 or more then the scale is reduced to
> avoid theoretical truncation of meaningful data. However, the minimum
> scale reached with this process is 6. And that's your situation.
>
> Try a little test:
>
> declare @amount1 decimal(28,10)
> declare @amount2 decimal(28,10)
> set @amount1 = 0.0275
> set @amount2 = 0.0105
> SELECT @amount1, @amount2
> SELECT
> SQL_VARIANT_PROPERTY(CAST(@amount1 * @amount2 AS
> SQL_VARIANT),'precision'), -- will be 38
> SQL_VARIANT_PROPERTY(CAST(@amount1 * @amount2 AS
> SQL_VARIANT),'scale') -- will be 6 (!)
>
> And another one:
>
> declare @amount1 decimal(28,10)
> declare @amount2 decimal(18,10) -- changed precision to 18, so the
> calculated precision of the result is 46
> set @amount1 = 0.0275
> set @amount2 = 0.0105
> SELECT @amount1, @amount2
> SELECT
> SQL_VARIANT_PROPERTY(CAST(@amount1 * @amount2 AS
> SQL_VARIANT),'precision'), -- will be 38 (as this is the maximum precision
> available)
> SQL_VARIANT_PROPERTY(CAST(@amount1 * @amount2 AS
> SQL_VARIANT),'scale') -- will be 11 (because 11 = s1 + s2 - [p1 + p2 - 37]
> = 10 + 10 - [28+18-37])
>
> IMHO, this is not the worst part of numeric operations. Understanding of
> precision and scale after division is the real challenge :-)
>
> --
> Regards
> Pawel Potasinski
> [
>
>
> U¿ytkownik "Atenza" <> napisa?w wiadomo¶ci
> news:.gbl...
>> declare @amount1 decimal(28,10)
>> declare @amount2 decimal(28,10)
>> set @amount1 = 0.0275
>> set @amount2 = 0.0105
>> select @amount1 * @amount2
>>
>> i expect the result = 0.00028875, however, it only return 0.000289 why??
>> the declartion of the decimal data type specify the decimal place up to
>> 10, how come after mulitple, only 6 decimal places return? please help
>> me!
>>
>> Thanks in advance!
>> Martin
>>
>
>

Pawel Potasinski

> SQL_VARIANT_PROPERTY(CAST(@amount1 * @amount2 AS SQL_VARIANT),'scale')
> -- will be 11 (because 11 = s1 + s2 - [p1 + p2 - 37] = 10 + 10 -
> [28+18-37])
>
> How do you know the scale is s1 + s2 - [p1 + p2 - 37]?

By doing a very simple research :-) Just track how result scale is changing
when you increase the precision of input numbers.

> From the BOL ("Precision, Scale, and Length (Transact-SQL)" chapter), only
> s1 + s2 will be the result scale after e1 * e2
> Except 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.

BOL does not always explain everything. And sometimes it contains
mistakes... I always perform some tests when I'm not sure if BOL says the
truth ;-)

>
> this is not the worst part of numeric operations <-- It seems that you
> have encountered worst numeric operation that this, i am quite
> interesting, would you mind to share?

What I mean is the result precision and scale of the division. When you
multiply two decimals precision and scale is fairly easy to calculate (the
problem appears when the maximum precision is reached). But when you divide
the expressions for precision and scale calculation are pretty complex. So
it is a little harder to make the research. Especially only by using T-SQL
;-)

Finally, IMHO, decimal data type is quite good for calculations that do not
need scale greater than 6 (or even smaller...).

Regards,
Pawel

Atenza

Thank you very much!!
It seems that there is no easy way to solve in my situation.

"Ben Nevarez" <> wrote in message
news:...
>
> Hi Martin,
>
> Probably the explanation is here
>
> Precision, Scale, and Length (Transact-SQL)
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/fbc9ad2c-0d3b-4e98-8fdd-4d912328e40a.htm
>
> For the operation e1 * e2, with precision p1 and scale s1, and precision
> p2
> and scale s2, the precision for the result will be p1 + p2 + 1 and the
> scale
> for the result will be s1 + s2. But there is a note saying that if a
> precision is greater than 38, the scale is "reduced to prevent the
> integral
> part of a result from being truncated".
>
> For example if you use
>
> declare @amount1 decimal(18,10)
> declare @amount2 decimal(18,10)
> set @amount1 = 0.0275
> set @amount2 = 0.0105
> select @amount1 * @amount2
>
> you get
>
> 0.00028875000000000000
>
> where the scale is s1 + s2 = 10 + 10 = 20, and the precision is less than
> 38.
>
> Hope this helps,
>
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
>
>
> "Atenza" wrote:
>
>> declare @amount1 decimal(28,10)
>> declare @amount2 decimal(28,10)
>> set @amount1 = 0.0275
>> set @amount2 = 0.0105
>> select @amount1 * @amount2
>>
>> i expect the result = 0.00028875, however, it only return 0.000289 why??
>> the declartion of the decimal data type specify the decimal place up to
>> 10,
>> how come after mulitple, only 6 decimal places return? please help me!
>>
>> Thanks in advance!
>> Martin
>>
>>
>>

Atenza

SQL_VARIANT_PROPERTY(CAST(@amount1 * @amount2 AS SQL_VARIANT),'scale')
-- will be 11 (because 11 = s1 + s2 - [p1 + p2 - 37] = 10 + 10 - [28+18-37])

How do you know the scale is s1 + s2 - [p1 + p2 - 37]?
From the BOL ("Precision, Scale, and Length (Transact-SQL)" chapter), only
s1 + s2 will be the result scale after e1 * e2
Except 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.

this is not the worst part of numeric operations <-- It seems that you have
encountered worst numeric operation that this, i am quite interesting, would
you mind to share?

Anyway, thank your for your kind explanation.
Martin ^_^

"Pawel Potasinski" <> wrote in message
news:e7ozG$.gbl...
> If you look into BOL ("Precision, Scale, and Length (Transact-SQL)"
> chapter) then you will find some table containing precision and scale of
> decimal data after arithmetic operations.
>
> And if you multiply two decimals: on of precision p1 and scale s1 and the
> second of precision p2 and scale s2 you gain precision (p3) and scale (s3)
> as shown below.
>
> p3 = p1 + p2 + 1 -> p3 = 28 + 28 + 1 = 57 ???
> s3 = s1 + s2 -> s3 = 10 + 10 = 20
>
> As you may know the maximum precision of decimal is 38. If the calculated
> precision of the result reaches 37 or more then the scale is reduced to
> avoid theoretical truncation of meaningful data. However, the minimum
> scale reached with this process is 6. And that's your situation.
>
> Try a little test:
>
> declare @amount1 decimal(28,10)
> declare @amount2 decimal(28,10)
> set @amount1 = 0.0275
> set @amount2 = 0.0105
> SELECT @amount1, @amount2
> SELECT
> SQL_VARIANT_PROPERTY(CAST(@amount1 * @amount2 AS
> SQL_VARIANT),'precision'), -- will be 38
> SQL_VARIANT_PROPERTY(CAST(@amount1 * @amount2 AS
> SQL_VARIANT),'scale') -- will be 6 (!)
>
> And another one:
>
> declare @amount1 decimal(28,10)
> declare @amount2 decimal(18,10) -- changed precision to 18, so the
> calculated precision of the result is 46
> set @amount1 = 0.0275
> set @amount2 = 0.0105
> SELECT @amount1, @amount2
> SELECT
> SQL_VARIANT_PROPERTY(CAST(@amount1 * @amount2 AS
> SQL_VARIANT),'precision'), -- will be 38 (as this is the maximum precision
> available)
> SQL_VARIANT_PROPERTY(CAST(@amount1 * @amount2 AS
> SQL_VARIANT),'scale') -- will be 11 (because 11 = s1 + s2 - [p1 + p2 - 37]
> = 10 + 10 - [28+18-37])
>
> IMHO, this is not the worst part of numeric operations. Understanding of
> precision and scale after division is the real challenge :-)
>
> --
> Regards
> Pawel Potasinski
> [
>
>
> U¿ytkownik "Atenza" <> napisa?w wiadomo¶ci
> news:.gbl...
>> declare @amount1 decimal(28,10)
>> declare @amount2 decimal(28,10)
>> set @amount1 = 0.0275
>> set @amount2 = 0.0105
>> select @amount1 * @amount2
>>
>> i expect the result = 0.00028875, however, it only return 0.000289 why??
>> the declartion of the decimal data type specify the decimal place up to
>> 10, how come after mulitple, only 6 decimal places return? please help
>> me!
>>
>> Thanks in advance!
>> Martin
>>
>
>

Pawel Potasinski

> SQL_VARIANT_PROPERTY(CAST(@amount1 * @amount2 AS SQL_VARIANT),'scale')
> -- will be 11 (because 11 = s1 + s2 - [p1 + p2 - 37] = 10 + 10 -
> [28+18-37])
>
> How do you know the scale is s1 + s2 - [p1 + p2 - 37]?

By doing a very simple research :-) Just track how result scale is changing
when you increase the precision of input numbers.

> From the BOL ("Precision, Scale, and Length (Transact-SQL)" chapter), only
> s1 + s2 will be the result scale after e1 * e2
> Except 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.

BOL does not always explain everything. And sometimes it contains
mistakes... I always perform some tests when I'm not sure if BOL says the
truth ;-)

>
> this is not the worst part of numeric operations <-- It seems that you
> have encountered worst numeric operation that this, i am quite
> interesting, would you mind to share?

What I mean is the result precision and scale of the division. When you
multiply two decimals precision and scale is fairly easy to calculate (the
problem appears when the maximum precision is reached). But when you divide
the expressions for precision and scale calculation are pretty complex. So
it is a little harder to make the research. Especially only by using T-SQL
;-)

Finally, IMHO, decimal data type is quite good for calculations that do not
need scale greater than 6 (or even smaller...).

Regards,
Pawel