SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fun with Scale and Precision


Fun with Scale and Precision

Author
Message
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8887 Visits: 7281
All good and well, however, the explanation re why the rounding off takes place is.....?

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
sql.selflearn
sql.selflearn
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 0
0.0001670000
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3702 Visits: 4408
stewartc-708166 (12/3/2009)
the explanation re why the rounding off takes place is.....?

Something about this can be found in BOL, topic "Precision, Scale, and Length (Transact-SQL)": http://technet.microsoft.com/en-us/library/ms190476.aspx

We have two numbers of type NUMERIC(38,10), so their precision = 38 and scale = 10.
According to the table from the above link, the result precision is: p1 - s1 + s2 + max(6, s1 + p2 + 1) = 38 - 10 + 10 + max(6, 10 + 38 + 1) = 38 + 49 = 87.
The result scale is: max(6, s1 + p2 + 1) = max(6, 10 + 38 + 1) = 49.

But there is also a note:
* 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.

Ok, the result precision (87) is definitely greater than 38, so it was reduced to 38. But why the scale was reduced to 6 – I can't find any explanation Ermm
archie flockhart
archie flockhart
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1320 Visits: 1154
INterestingly, and counter-intuitively, at least for me ...

SELECT cast(1.67574 as decimal(38,10)) /cast(10000 as decimal(38,10))
gives this result: .000167

While
SELECT cast(1.67574 as decimal(38,10)) /cast(10000 as decimal(38,1))
gives this more accurate result.000167574
Colin Frame
Colin Frame
Old Hand
Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)

Group: General Forum Members
Points: 309 Visits: 342
The missing digits is due to the initial casts - with decimal, they are automatically converted to the minimum numeric precision and scale required before the calculation is done, so 1.67574 ends up with a scale of 5 and 10000 with a scale of 0. I would have thought this would lead to an initial result with scale of 5 i.e. 0.00016 and a final result of 0.0001600000 so I'm wondering how the 6th digit gets retained.

Forum Etiquette: How to post data/code on a forum to get the best help
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Colin Frame
Colin Frame
Old Hand
Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)

Group: General Forum Members
Points: 309 Visits: 342
This explains it:
http://msdn.microsoft.com/en-us/library/aa258274(SQL.80).aspx

For division:
[EDITED - was incorrect, apologies CFF]
scale of the result = max(6, s1 + p2 + 1)
where s1 is the scale of the numerator and p2 is the precision of the denominator.

Forum Etiquette: How to post data/code on a forum to get the best help
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Dude76
Dude76
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 94
declare
@d1 decimal(38,10),
@d2 decimal(38,10)
SELECT
@d1 = 1.67574,
@d2 = 10000
SELECT
d1 = @d1,
d2 = @d2,
d1_div_d2 = @d1 / @d2,
cast_div = CAST(@d1 / @d2 AS DECIMAL(38,10)),
num_div = 1.67574 / 10000.0,
cast_num_div = CAST(1.67574 / 10000.0 AS DECIMAL(38,10))


done (i trimed strings to results)
d1           d2               d1_div_d2 cast_div     num_div        cast_num_div
------------ ---------------- --------- ------------ -------------- ------------
1.6757400000 10000.0000000000 0.000167 0.0001670000 0.000167574000 0.0001675740


w00tHeheCrazy:-D


My MCP Transcript (ID : 692471 Access : 109741229)
nadabadan
nadabadan
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 1018
colin.frame (12/4/2009)
This explains it:
http://msdn.microsoft.com/en-us/library/aa258274(SQL.80).aspx

For division:
scale of the result = s1 + s2 + 1
where s1 and s2 are the scales of the initial numbers.


Nowhere is "s1+s2+1" mentioned in the link provided.
mark.hammond
mark.hammond
SSC-Enthusiastic
SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)

Group: General Forum Members
Points: 148 Visits: 58
Odd that replacing the CAST(10000,NUMERIC(38,10)) with 10000 gives the more precise result 0.0001675740.

Also odd that Sybase 15.0.2 gives the result 0.0001675740 with the original query. You'd think running the SAME query on two ANSI-standard DBMSs would give the SAME result.
Carlo Romagnano
Carlo Romagnano
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4921 Visits: 3326
Explanation is in the note.
Try to reduce the precision.
SELECT CAST(cast(1.67574 as decimal(38,10)) / cast(10000 as decimal(38,10)) AS DECIMAL(38,10) ) conv_factor
returns 0.0001670000
SELECT CAST(cast(1.67574 as decimal(29,10)) / cast(10000 as decimal(29,10)) AS DECIMAL(29,10) ) conv_factor
returns 0.0001675740

I run on tuttopodismo
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