1

Topic: decimal (20, 11) yields less exact result than decimal (18, 9)

I fondly thought that the more in decimal is led out on " an amount of discharges which can be allocated to the right of a decimal symbol ", the calculation will be more exact.
That is I expected that the type "decimal (20, 11)" gives big (or at least not smaller) accuracy than decimal (18, 9).
There was at me a program piece (a SQL Server 2012) like such

CREATE TABLE #Tmp (
AmountToRelease decimal (18, 9)
,ShareToRelease decimal (18, 9)
,ShareToRelease1stApproach decimal (18, 9)
,Dest decimal (18, 9)
)
INSERT INTO #Tmp
(
AmountToRelease
,ShareToRelease
,ShareToRelease1stApproach
)
VALUES
(0.823673237;
0.123456781;
100.987654329
)
UPDATE #Tmp
SET Dest = AmountToRelease * (ShareToRelease / ShareToRelease1stApproach)
select * from #Tmp
drop table #Tmp

It yielded result in the field #Tmp.Dest = 0.001006935.
"Absolutely exact" result should be "0.823673237 * (0.123456781 / 100.987654329) = 0.0010069354230626 ".
Therefore I decided to increase accuracy of all decimal with (18, 9) on (20, 11)
Also rewrote the code so:

CREATE TABLE #Tmp (
AmountToRelease decimal (20, 11)
,ShareToRelease decimal (20, 11)
,ShareToRelease1stApproach decimal (20, 11)
,Dest decimal (20, 11)
)
INSERT INTO #Tmp
(
AmountToRelease
,ShareToRelease
,ShareToRelease1stApproach
)
VALUES
(0.823673237;
0.123456781;
100.987654329
)
UPDATE #Tmp
SET Dest = AmountToRelease * (ShareToRelease / ShareToRelease1stApproach)
select * from #Tmp
drop table #Tmp

It yielded result in the field #Tmp.Dest = 0.00100694000 .
That is it even is worse than was at decimal (18, 9).
Why? Also what with it to do?

2

Re: decimal (20, 11) yields less exact result than decimal (18, 9)

KellyLynch;
SET Dest = AmountToRelease* CAST ((ShareToRelease / ShareToRelease1stApproach) as FLOAT)

3

Re: decimal (20, 11) yields less exact result than decimal (18, 9)

KellyLynch;
What do you want from the computer when you use a cheap variant of a floating point?

4

Re: decimal (20, 11) yields less exact result than decimal (18, 9)

TaPaK wrote:

KellyLynch;
SET Dest = AmountToRelease* CAST ((ShareToRelease / ShareToRelease1stApproach) as FLOAT)

Yes is worked. Accuracy raised; thanks.
Now it would be desirable explanations it:
- Why so happens?
- Where it ?
- On what versions of a SQL Server it happens?

5

Re: decimal (20, 11) yields less exact result than decimal (18, 9)

Kolosov wrote:

KellyLynch;
What do you want from the computer when you use a cheap variant of a floating point?

Explain :-); was not in time behind a course of your thought....

6

Re: decimal (20, 11) yields less exact result than decimal (18, 9)

KellyLynch;
Read in help that such DECIMAL and FLOAT, it becomes clear.

7

Re: decimal (20, 11) yields less exact result than decimal (18, 9)

KellyLynch wrote:

Where it ?

Accuracy, scale and length
It is not assured, whether it that you want.

8

Re: decimal (20, 11) yields less exact result than decimal (18, 9)

iap wrote:

it is passed...
Accuracy, scale and length
It is not assured, whether it that you want.

Aha, it, forgot as to ask smile

9

Re: decimal (20, 11) yields less exact result than decimal (18, 9)

TaPaK wrote:

it is passed...
Aha, it, forgot as to ask smile

Thanks, I esteem.
For now you could not answer a question - and can, the variant would give still the big accuracy than yours more low?:

SET Dest = AmountToRelease * (CAST (ShareToRelease as float) / CAST (ShareToRelease1stApproach as float))

10

Re: decimal (20, 11) yields less exact result than decimal (18, 9)

KellyLynch;
I.e. article you not ?

11

Re: decimal (20, 11) yields less exact result than decimal (18, 9)

TaPaK wrote:

KellyLynch;
I.e. article you not ?

Read, and not once :-); simply long could not correlate that that I read - with the source code "SET Dest = AmountToRelease * (ShareToRelease / ShareToRelease1stApproach)" and corrected "SET Dest = AmountToRelease* CAST ((ShareToRelease / ShareToRelease1stApproach) as FLOAT)".
Now like thought; but it is not quite assured.
You could not check up correctness of my reasonings:
1. Understood - why magnification of accuracy of my fields (with decimal (18, 9) to decimal (20, 11) on the contrary, led to "impairment of result of calculations". Because there was a situation about which it is told in article as " * Accuracy and result scale have the absolute maximum equal 38. If value of accuracy exceeds 38, the appropriate scale decreases, whenever possible, to prevent truncation of an integral part of result. "
Therefore for example at multiplication of two decimal (18, 9) this situation did not happen: "Accuracy of result" was p1 + p2 + 1 = 18 + 18 + 1 = 37 that less than 38.
And here if to increase two decimal (20, 11): "Accuracy of result" was calculated as p1 + p2 + 1 = 20 + 20 + 1 = 41 that it is more 38. In this case the SQL Server did that that is named " appropriate scale decreases "; because of what at me numbers to the right of a decimal symbol "were lost"
2. Understood - why "SET Dest = AmountToRelease* CAST ((ShareToRelease / ShareToRelease1stApproach) as FLOAT)" began to yield the best result. As "CAST ((ShareToRelease / ShareToRelease1stApproach) as FLOAT" is FLOAT and "AmountToRelease* CAST ((ShareToRelease / ShareToRelease1stApproach) as FLOAT)" began to be considered as product two FLOAT (which only then, at record in the field decimal (20, 11), it was led decimal). That is here there was no that "loss",  happened in point 1.
Therefore the result appeared better.
3. And now the complicated question - and whether is sense still to refine "SET Dest = AmountToRelease* CAST ((ShareToRelease / ShareToRelease1stApproach) as FLOAT)", making of it "SET Dest = AmountToRelease * (CAST (ShareToRelease as float) / CAST (ShareToRelease1stApproach as float))"?
It seems to me that is. Now ("CAST ((ShareToRelease / ShareToRelease1stApproach) as FLOAT") at first it is considered division decimal (20, 11) on decimal (20, 11); and only then it will be transformed to FLOAT.
"Division decimal (20, 11) on decimal (20, 11)" conducts to that that (as it is told in article) "Accuracy of result" will be defined as p1 - s1 + s2 + max (6, s1 + p2 + 1) = 20 - 11 + 11 + max (6, 11 + 20 + 1) = 52. It is more than 38; that is also operation " appropriate scale will take place here decreases, whenever possible, to prevent truncation of an integral part of result ".
And if to make "SET Dest = AmountToRelease * (CAST (ShareToRelease as float) / CAST (ShareToRelease1stApproach as float))" will share at once FLOAT on FLOAT; and operations " the appropriate scale decreases, whenever possible, to prevent truncation of an integral part of result " does not happen.
That should give "still the best" result.
Well - what you found (if found) errors in these reasonings?

12

Re: decimal (20, 11) yields less exact result than decimal (18, 9)

KellyLynch;
Now read a priority , and a priority of types.

13

Re: decimal (20, 11) yields less exact result than decimal (18, 9)

And figs I will give you fish smile learn to catch

14

Re: decimal (20, 11) yields less exact result than decimal (18, 9)

And it is enough for the given example

wrote:

SET Dest = CAST (AmountToRelease as FLOAT) * ShareToRelease/ShareToRelease1stApproach

Above there was an example simply from a habit not assort all

15

Re: decimal (20, 11) yields less exact result than decimal (18, 9)

TaPaK wrote:

and it is enough for the given example
it is passed...
Above there was an example simply from a habit not assort all

About - thanks, this that that is necessary.
In turn I see that there is a sense to increase initial type (in my table #Tmp) with decimal (18, 9) at all to decimal (20, 11) (as I thought at first), and to decimal (26, 17 ).
Why it is no more? Because MSDN https://docs.microsoft.com/en-us/sql/t- … ansact-sql speaks
"Conversion of float values that use scientific notation to decimal or numeric is restricted to values of precision 17 digits only. Any value with precision higher than 17 rounds to zero."
That is to do not decimal (26, 17) and decimal (27, 18) any more will not be sense.
I tried, and received here that:
"Absolutely exact" the result (counted on Windows Calculator) should be "0.823673237 * 0.123456781 / 100.987654329 = 0.00100693542306258884687437406337".
.
The result with usage of your code " SET Dest = CAST (AmountToRelease as FLOAT) * ShareToRelease/ShareToRelease1stApproach " and with decimal (20, 11) gives 0.00100693542.
The result with usage of your code " SET Dest = CAST (AmountToRelease as FLOAT) * ShareToRelease/ShareToRelease1stApproach " and with decimal (26, 17 ) gives 0.00100693542306259.
Well - it seems, all is made that is possible to receive " more exact result".