July 28, 2021 at 12:00 am
Comments posted to this topic are about the item High Precision Multiplication
July 28, 2021 at 7:15 am
Thank you for authoring this article.
Is there any way to force SQL to stop truncating vs implementing a function?
WHS
July 28, 2021 at 11:10 am
In days of old when I was on a system that supported but two numbers to the right of the decimal point, the work around was to multiply by 10, or 100, or so on, and then manually correct the decimal point location.
July 28, 2021 at 11:15 am
Strange though it seems, there is a reason. You're multiplying two numbers where precision = 38 and scale = 10. The product requires precision = 77 (38 + 38 + 1) and scale = 20 (10 + 10). See this article which describes how the resulting precision and scale are derived:
https://cuisinecode.blogspot.com/2014/04/losing-precision-after-multiplication.html
The resulting decimal(77,20) exceeds the max precision of 38, so SQL reduces the resulting precision to 38. In order that it doesn't lose all the fractional places, it defaults to keeping 6 fractional digits hence your result of 1234.123457. Then you use Convert which adds the extra zeroes.
As an experiment I tried this:
DECLARE
@d1 DECIMAL(38, 18) = 1234.123456789012345678,
@d2 DECIMAL(2, 1) = 1.0
SELECT CONVERT(DECIMAL(38, 18), @d1 * @d2)
-- 1234.123456789012345700
The product requires precision = 41 (38 + 2 + 1) and scale = 19 (18 + 1). Decimal(41,19) isn't allowed so SQL would round to Decimal(38,16) which is what the result it.
So this is another "strange but true" thing which I didn't know about SQL Server until reading your question!
July 28, 2021 at 2:17 pm
Hi WHS,
I am not sure of what you mean by "vs implementing a function". The only way I know to prevent SQL Server from truncating is to perform the truncation yourself by using "smaller" types, like in William example where @d2 is declared as a DECIMAL(2, 1).
July 29, 2021 at 2:13 am
Reading between the lines, given the 18 decimal points, I'm assuming that you are storing Ether (or some other cryptocurrency). Based on @william Rayer's explanation, maybe you should store balances in Wei and do the Wei to Ether conversion in the presentation layer.
Sincerely,
Daniel
July 29, 2021 at 1:32 pm
Hi Daniel,
I actually store shares and votes per share. Most corporations have outstanding shares that are often big integers and votes per share that are small integers (typically 1 but you may encounter other values like 1.5, 5 or 10). Nevertheless, there are some jurisdictions around the world that fix an upper limit to the number of issued shares. If that limit is, for example, 100, then fractions of shares are traded and this is why I need a lot of significant digits at the left AND at the right of the decimal point.
If I want to calculate the percentage of vote of any given holder, I have to perform the scalar product of outstanding shares and votes per shares. For example, Holder X may hold 100,000,000 common shares of corporation ABC at 1 vote per share and 0.123456789 preferred shares at 10 votes per share, which gives 100,000,000 * 1 + 0.123456789 * 10 = 100,000,001,23456789 votes. Dividing this number by the total number of votes of corporation ABC (assume the value is 123,456,789 ) gives the percentage of votes of Holder X in corporation ABC (81.00000173709993%).
We obviously do not need such precision if all we intend is to display the holders of corporation ABC. 81% is as good as 81.00000173709993%. However, if corporation ABC holds 41.2345678% of corporation DEF and corporation DEF holds 25% of corporation GHI, then holder X indirectly holds 81.00000173709993% * 41.2345678% * 25% of corporation GHI.
I hope you get my drift. I use recursive CTE to calculate these indirect percentages. If numbers are truncated at every step of the process, we may end up having, for example, some holder indirectly hold 49,999720% of a given corporation where the truth is 50%. Here, a minority holder becomes a majority holder and that makes all the difference in the world.
You may call this ether, I call it daily business.
Regards.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply