Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Decimal Truncation in division


Decimal Truncation in division

Author
Message
dutt
dutt
Mr or Mrs. 500
Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)

Group: General Forum Members
Points: 506 Visits: 33
Hi

Look at these SELECT statements.

1. SELECT CAST(1.67574 AS DECIMAL(38,10)) A, CAST(10000 AS DECIMAL(38,10)) B

2. SELECT CAST(1.67574 AS DECIMAL(38,10)) / CAST(10000 AS DECIMAL(38,10)) conv_factor

When first statement is run the result is
A B
1.6757400000 10000.0000000000

But, when the second one runs, the result is

conv_factor
0.000167

Both the numbers are converted into DECIMAL(38,10) before the division, but, why did this truncation occured?

Thanks.
Dutt.
wschampheleer
wschampheleer
SSC Eights!
SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)SSC Eights! (870 reputation)

Group: General Forum Members
Points: 870 Visits: 303
The result of

SELECT CAST(1.67574 AS DECIMAL(38,10)) / CAST(10000 AS DECIMAL(38,10)) conv_factor



has datatype decimal(38,6). From BOL: In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3.

To get the desired outcome, use

SELECT CAST(1.67574/10000 AS DECIMAL(38,10)) conv_factor



Regards,

Willem
http://wschampheleer.wordpress.com
Dave Ballantyne
Dave Ballantyne
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2022 Visits: 8370
Or another option

SELECT 1.67574/10000.0 as  conv_factor





Clear Sky SQL
My Blog
dutt
dutt
Mr or Mrs. 500
Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)

Group: General Forum Members
Points: 506 Visits: 33
Got it buddies.... thanks.
Should have checked BOL before posting ;-)
Paul White
Paul White
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11132 Visits: 11353
wschampheleer (2/22/2010)
The result of
SELECT CAST(1.67574 AS DECIMAL(38,10)) / CAST(10000 AS DECIMAL(38,10)) conv_factor

has datatype decimal(38,6).

True.

wschampheleer (2/22/2010)
From BOL: In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3.

Also true, but it does not explain what is going on here.

It is true that the literal value 1.67574 would be implictly a DECIMAL(6,5). But, the CAST converts that explicitly to DECIMAL(38,10).
A similar thing is true for the literal value 10000: implied DECIMAL(5,0). But, again, explicitly CAST to DECIMAL(38,10).

So, we have two DECIMAL(38,10) values used with the division operator (/). What will be the scale and precision of the result?

Well, in this case, both operands have the same base type (decimal) so we don't need to worry about type conversion.
There is a formula for the precision and scale of this division result, contained in the reference at the end of this post.

The formula applies to two expressions e1 and e2, with precision p1 and p2, and scale s1 and s2.

In our case:
p1 = 38
p2 = 38
s1 = 10
s2 = 10

The formula for the result precision is:
(p1 - s1 + s2) + max(6, s1 + p2 + 1)
= (10 - 38 + 38) + max(6, 38 + 10 + 1)
= (10) + 49
= 59

The formula for the result scale is:
max(6, s1 + p2 + 1)
= max(6, 38 + 10 + 1)
= 49

(the imaginary max function here just returns the highest of the values passed to it)

So, our result is a DECIMAL(59,49). That is a problem since SQL Server has a maximum precision of 38 (currently). Luckily, there is a rule for this scenario too:

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.

It turns out that SQL Server never reduces the scale to less than 6 (the same magic number as in the max formulas before), and that is what happens here. There is no way divide one DECIMAL(38,10) by another and retain enough precision and scale for all valid values.

SQL Server does not take account of the actual values stored in the expressions - it applies rules which produce predictable results depending only on the precision and scale of the inputs. It makes no difference therefore, that our expected result (0.000167574) would fit in a DECIMAL(10,10) - application of the rules for dividing a DECIMAL(38,10) by a DECIMAL(38,10) result in a DECIMAL(38,6) - the maximum value for precision, and the minimum value for scale.

The essential point to take away from this is to never use a greater precision or scale than you have a sensible need for. If the original query had been this:

SELECT CAST(1.67574 AS DECIMAL(6,5)) / CAST(10000 AS DECIMAL(5,0)) AS conv_factor



...the result would be 0.00016757400 - a DECIMAL(12,11).

I'm not suggesting that only decimals of exactly the right size should ever be used - but consider storage size and the potential need for increased precision when arithmetic functions are performed.

Paul


Reference: Precision, Scale, and Length




Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search