Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by mark.stockwell » Fun with Scale and Precision
Add to briefcase
33 posts, Page 1 of 4
1234»»»

Fun with Scale and Precision Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Site Owners
Posted Thursday, December 03, 2009 10:38 PM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,040, Visits: 1
Comments posted to this topic are about the item Fun with Scale and Precision
Post #828643
Stewart "Arturius" Campbell
Posted Thursday, December 03, 2009 11:55 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:48 AM
Points: 3,125, Visits: 4,311
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”
Post #828655
sql.selflearn
Posted Friday, December 04, 2009 12:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 04, 2009 12:37 AM
Points: 1, Visits: 0
0.0001670000
Post #828667
vk-kirov
Posted Friday, December 04, 2009 1:28 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:42 AM
Points: 3,189, Visits: 4,146
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
Post #828680
archie flockhart
Posted Friday, December 04, 2009 1:53 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 5:13 AM
Points: 1,123, Visits: 986
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
Post #828686
colin.frame
Posted Friday, December 04, 2009 2:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 3:31 AM
Points: 92, Visits: 130
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.
Post #828708
colin.frame
Posted Friday, December 04, 2009 2:58 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 3:31 AM
Points: 92, Visits: 130
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.
Post #828713
Dude76
Posted Friday, December 04, 2009 5:05 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, March 18, 2013 7:52 PM
Points: 280, Visits: 88 
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

My MCP Transcript (ID : 692471 Access : 109741229)
Post #828761
nadabadan
Posted Friday, December 04, 2009 7:10 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 01, 2013 3:03 PM
Points: 317, Visits: 1,018
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.
Post #828880
mark.hammond
Posted Friday, December 04, 2009 7:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 7:20 AM
Points: 113, Visits: 52
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.
Post #828900
« Prev Topic | Next Topic »

Add to briefcase
33 posts, Page 1 of 4
1234»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.