August 30, 2016 at 4:44 am
Jeff Moden (8/29/2016)
Sergiy (8/29/2016)
Jeff Moden (8/29/2016)
vdiazh (8/28/2016)
Is great, thanksI hope you're not using the FLOAT version. FLOAT is a binary "approximation" of numbers and it the scale varies according to how many numbers are used to the left of the decimal point, which is a disadvantage in this case.
There is no better data type for "rate" values than float.
Any kind of decimal will be less precise than float.
Someone would have to prove that one to me, Sergiy. I DO understand the benefits of FLOAT for certain things but don't believe that things like amortization tables fit the bill here. Of course, that's based on a previous bad experience and I may have done something incorrectly. Unfortunately, I no loger have the code that cause the "penny skew".
No-one will prove it to you Jeff, because your instinct is right, and float is not more acurate that all deimal types. Float's best accuracy is 56 bits but decimal (38,19) has more than 100 bits accuracy. But that's not a good reaon for not using floats in some circumstances, where that level of accuracy is not needed (for example in the step from one payment date to the next when computing an amortization table); in fact the reason for not storing the amortization table using float is that float is too accurate and risks gstoring incorrect amounts like 100.0000237 cents when the required value is 100.00 cents, whereas use of (for example) decimal(14,2) avoids that problem. Actually you usually can store the numbers in an amortization table as float if you really want to, but only if you convert them to decimal (to do the required rounding) and then back to float before storing them.
The values that have to be exact in an amortization table are (i) the mortgage rate quoted (be it annual or monthly or whatever) (ii) the amount payed in at each point, (iii) the initial balance and each balance after applying a payment, (iv) total amount payed (interest plus capital) so far at each point where a balance is calculated, (v) total capital and total interest to be paid at each of those points, and (v) period at which interest compounding occurs. These exact values must be valid values in the currency used (which may preclude the use of decimal - for example amounts in British or Irish currency before Feb 1971 couldn't be expressed in decimal unless it's expressed as a number of pennies rather than as a number of shillings or pounds, which generally wasn't acceptable, but more often enforces the use of decimal - eg current British, Euro, and American currencies). A rate that couldn't be expressed exactly as decimal would be rather unusual - when did you last see a mortgage rate of, for example, three and a third per cent?
If those values are not expressed exactly, you are doomed to getting wrong results. So don't store the values as float in the table (unless you round them appropriately first) even if you somehow have latest standard floating point (with decimal float instead of binary float - which isn't yet supported in SQL Server, although I hope to see that change before I get too old and gaga to notice).
All other values should be expressed with the maximum possible precision, but I don't see any reason for those other values ever to be in a table. The step from the end one period to the end of the next should begin by taking the current exact values from the table and converting the values to high precision types. (If you have a badly designed system you also have to convert the rate to a high precision value matching the period's duration - if your compounding interval isn't an exact multiple of the period your payment interval that calculation can be a real pain and and it will be the source of many errors if not sufficiently precise - and calculate the capital base for interest which may not be the same as the total balance in the last row, and not having the declared rate be for an exact multiple of the payment interval is just about guaranteed to produce errors in that.) Then calculate the values for the next row in the table (taking account of most recent interest and the current payment) and converting them all back to the appropriate exact type for storage in the table. You should probably use float for the "high precision" type, not because it's more precise than (for example) decimal(22,10) for typical amounts involved in these calculations (it isn't) but because it costs a lot less CPU power and lower store bus traffic.
You should not under any circumstances use the power function to calculate for several intervals at once, since doing so implies either using an accurate value at points where the rounded value is required or doing all cacluations at presentation precision, each of which may deliver incorrect results. Use instead the appropriate T-SQL windowed functions or if you are on a pre-ark version of SQL Server use either quirky update (as described by Jeff Moden for running totals) or (:sick:) a while loop to ensure that you compute each row and round its values before using it to compute the next row.
Tom
August 30, 2016 at 5:01 pm
Oh, gosh.
No wonder our industry has a bad name.
Tom, here is something for you (and others) to think about.
And verify your instincts.
DECLARE @D DECIMAL (38, 28), @F FLOAT
SET @D = 1.001 SET @F = 1.001
SELECT 0 [Number of Operations],
@D,
SQL_VARIANT_PROPERTY(@D, 'Precision') DecimalPrecision,
SQL_VARIANT_PROPERTY(@D, 'Scale') DecimalScale,
@F,
SQL_VARIANT_PROPERTY(@F, 'Precision') FloatPrecision,
SQL_VARIANT_PROPERTY(@F, 'Scale') FloatScale
UNION
SELECT 1 ,
CONVERT(DECIMAL (38, 28), @D*@D),
SQL_VARIANT_PROPERTY(@D*@D, 'Precision') DecimalPrecision,
SQL_VARIANT_PROPERTY(@D*@D, 'Scale') DecimalScale,
@F*@F,
SQL_VARIANT_PROPERTY(@F*@F, 'Precision') FloatPrecision,
SQL_VARIANT_PROPERTY(@F*@F, 'Scale') FloatScale
UNION
SELECT 2 ,
CONVERT(DECIMAL (38, 28), @D*@D*@D),
SQL_VARIANT_PROPERTY(@D*@D*@D, 'Precision') DecimalPrecision,
SQL_VARIANT_PROPERTY(@D*@D*@D, 'Scale') DecimalScale,
@F*@F*@F,
SQL_VARIANT_PROPERTY(@F*@F*@F, 'Precision') FloatPrecision,
SQL_VARIANT_PROPERTY(@F*@F*@F, 'Scale') FloatScale
UNION
SELECT 3 ,
CONVERT(DECIMAL (38, 28), @D*@D*@D*@D),
SQL_VARIANT_PROPERTY(@D*@D*@D*@D, 'Precision') DecimalPrecision,
SQL_VARIANT_PROPERTY(@D*@D*@D*@D, 'Scale') DecimalScale,
@F*@F*@F*@F,
SQL_VARIANT_PROPERTY(@F*@F*@F*@F, 'Precision') FloatPrecision,
SQL_VARIANT_PROPERTY(@F*@F*@F*@F, 'Scale') FloatScale
_____________
Code for TallyGenerator
August 30, 2016 at 11:54 pm
The problem is that MS made the decision to truncate to a scale of 6 whenever the precision of the DECIMAL datatype goes over 38 instead of doing what Granny's 4 function calculator does. MS also did things like round FLOAT so that things like 1/3*3 = 1 but only for certain things. Decision making tools like CASE use the underlying incorrect value while other calculations use the rounded value.
My original answer to this whole thread was going to be, "Don't do it in T-SQL because they've whammered up the math" unless you cast every bloody interim answer, like Granny's calculator does.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 31, 2016 at 12:14 am
Sergiy (8/30/2016)
Jeff Moden (8/29/2016)
Heh... let's hope the programmers in the IRS aren't using FLOAT to make decisions.You better hope they do.
Why - see below.
DECLARE @Amount MONEY, @Term INT
DECLARE @fRate float, @dRate decimal (38,15)
SELECT @Amount = 1000000, @Term = 17*3*13*7*19*23*43*11
SELECT @Term
SELECT @fRate = CONVERT(FLOAT, @Amount)/@Term,
@dRate = CONVERT(DECIMAL(38,15), @Amount)/@Term
SELECT @fRate [@fRate], @fRate * @Term / @fRate /@Term * @fRate * @Term fAmount,
@dRate [@dRate], @dRate * @Term / @dRate /@Term * @dRate * @Term dAmount
SELECT CASE
WHEN @fRate * @Term / @fRate /@Term * @fRate * @Term >= 1000000 THEN 'Paid in Full'
ELSE 'Go to Jail for not paid in full.'
END
, CAST(@fRate * @Term / @fRate /@Term * @fRate * @Term AS DECIMAL(38,28)) TrueFloatvalueIsWhyCaseFailed
FLOAT still comes up with the wrong answer but because of the rounding MS does to keep folks from flipping their wigs, lot's of folks are happy with FLOAT.
I can tell you a secret - folks from IRS do roundings too.
Rounding to the nearest cent.
In fact - everybody who operates with monetary values does.
Because there are no money units less than 1 cent.
Therefore, when you compare amounts you must make sure both compared values are presented in correct data type which would be appropriate for monetary amounts - MONEY in case of SQL Server.
So, it's not FLOAT comes up with a wrong answer, it's you come up with a wrong formula.
Correct one wold be :
SELECT CASEWHEN CONVERT(MONEY, @fRate * @Term / @fRate /@Term * @fRate * @Term) >= 1000000 THEN 'Paid in Full'
ELSE 'Go to Jail for not paid in full.'
END
, CAST(@fRate * @Term / @fRate /@Term * @fRate * @Term AS DECIMAL(38,28)) TrueFloatvalueIsWhyCaseFailed
BTW, the "TrueFloatvalueIsWhyCaseFailed" you display here is not really "True Float value", it's DECIMAL representation of true FLOAT value.
it's not quite the same.
Here is the proof:
DECLARE @FloatValue float, @DecimalValue DECIMAL(38,28)SELECT @FloatValue = CONVERT(FLOAT, 1) / 3
SELECT @DecimalValue = @FloatValue
SELECT@FloatValue, CONVERT(DECIMAL(38,28), @FloatValue * 3),
@DecimalValue, @DecimalValue * 3
The thing that sucks even worse is that if over 38 digits of precision are reached for any reason, MS decided to role back to just 6 or fewer digits of scale for the DECIMAL data type. So you have to do like Granny's calculator and resize the number after every calculation that occurs when she presses a mathematical operator button. Not fun.
MS do no role to REAL precision, they keep it as precise as possible.
And I must say - DECIMAL calculations use twice as deep computation precision comparing to FLOAT.
It's math of fixed point calculations which sucks.
Fixed decimal point - that's what kills precision.
When you divide DECIMAL (38,28) by 1000 you get a number of DECIMAL (35, 25).
If you multiply it again by the same 1000 you cannot get your 3 lost precise digits back.
So, your real precision will be DECIMAL(38,25).
Any further operation on this number will steal more truthful digits from it:
DECLARE @Amount MONEY, @Term INTDECLARE @fRate float, @dRate decimal (38,28)
DECLARE @i INT, @fOutput FLOAT, @dOutput DECIMAL (38,28)
SELECT @Amount = 1000000, @Term = 12*17
SELECT@fRate = CONVERT(FLOAT, @Amount)/@Term,
@dRate = CONVERT(DECIMAL(38,28), @Amount)/@Term
SELECT@fOutput = @fRate * @Term,
@dOutput = @dRate * @Term
SET @I = 5
WHILE @i > 0
BEGIN
SET @fOutput = @fOutput / @fRate /@Term * @fRate * @Term
SET @dOutput = @dOutput / @dRate /@Term * @dRate * @Term
SELECT @fOutput, @dOutput
SET @I = @I - 1
END
As you can see, the precision of decimals decreases with every iteration. And you cannot do anything about it.
To keep the precision on the same level you need to use DECIMAL(76, 56) for the result of multiplication/division operations.
BTW, I never said FLOAT is perfect in terms of precision.
FLOAT uses a limited number of bytes, therefore it has a limited presision, of course.
I said that FLOAT is the best of them all.
It's way more precise than any of DECIMAL data types.
In your example I calculated for how much money you wanted me to go to prison:
, @fRate * @Term / @fRate /@Term * @fRate * @Term - 1000000 [Not paid amount],It returned -1.16415321826935E-10.
Well, a judge would be impressed.
Not sure how he/she would want me to repay this amount though.
Let's see what will "precise" decimals give us:
SELECT CASEWHEN CONVERT(MONEY, @dRate * @Term / @dRate /@Term * @dRate * @Term) >= 1000000 THEN 'Paid in Full'
ELSE 'Go to Jail for not paid in full.'
END,
@dRate * @Term / @dRate /@Term * @dRate * @Term - 1000000 [Not paid amount]
Output:
----------------------------------------------
Go to Jail for not paid in full.-410.086678
A bit more than one hundredth of a millionth of a cent.
Here collectors would have a reason for a quick roundtrip to my house.
Don't you think?
I guess the conclusion is obvious :
Never ever use DECIMAL data types for loan calculations.
FLOAT and only FLOAT.
But do not forget to convert the final number to MONEY.
BTW... that's a hell of a TERM for a loan. Even if the PERIOD is an hour for that TERM, it's still almost 110,000 years. I hope you live that long and I hope the last voice you hear is mine. 😛
I used 17 years in my last example. DECIMAL still is proven not good enough to be used for loan calculations.
I agree... MS made a mess of the DECIMAL datatype especially with the rounding to a scale of 6 when a precision over 38 would be the result. They also made a mess of FLOAT. It's not that someone would send anyone to jail for a couple of billionths of a penny... it's that the answer is wrong and CASE doesn't use the rounded-to-prevent-human-hair-fires answer with CASE. I agree that such answers should be rounded to the nearest penny before being used if you're working with dollars or similar decimal currencies but it's not always dollars that you're working with. I wouldn't want to be in a rocket headed for Mars if someone did the calculations using SQL Server data types, especially if any kind of cascading calculations were made.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 31, 2016 at 6:33 am
Jeff, I usually quick on blaming MS for this and that.
But in this case they cannot be blamed.
They did not create any mess.
Because there is no mess.
It's all done right, exactly according to the rules of fixed point computations.
I'm an old man, and my memory cannot be trusted. :w00t:
But I can remember things which some people have forgotten, and some never heard of.
Amongst others, I can remember that when I studied FORTRAN in university (mid-80s) the issue with fixed point computations was exactly the same.
And there was no any element of surprise, or any attempt to put a blame on FORTRAN implementation, because before FORTRAN I studied "higher math" (as we named it), and questions of precision, truthful digits, systemic errors of different types of computations were a quite significant part of the curriculum.
What we see is embedded deficiencies of fixed point computations.
Which make them so imprecise in most of cases, that they simply are not allowed in real science.
The idiotic statement from MS about precise decimals and approximate floats means only there is not a single person in MS who has a reasonable level of math education.
Or - if such person exists on their roster he/she does not have a say on what the publish in their documentation.
P.S. Granny's calculator suffers from loosing precision too.
Same thing - it may show you 14 digits of a multiplication result, but it takes some math understanding to figure out how many of those digits are true.
The advantage of Granny's calculator is in the fact that it does 1 operation at the time, and after each single operation it converts result to the original scale-precision.
If in my example you add conversion to DECIMAL(38,28) for the result of each multiplication/division then you'll see the deviation getting smaller.
But still - with DECIMALs you'll never get the same level of precision as with FLOATs.
_____________
Code for TallyGenerator
August 31, 2016 at 6:49 am
it's that the answer is wrong and CASE doesn't use the rounded-to-prevent-human-hair-fires answer with CASE
Jeff, what do you say when somebody complains that he/she cannot find today's transactions by querying
WHERE TransactionDateTime = '20160831'
?
You say - learn how to write the query correctly.
Here we have exactly the same case.
You intend to compare 2 monetary amounts, of of which is given, another one is the result of your computations.
Monetary amount is money. Money means the whole number of smallest monetary units. Not decimal, but whole number of cents.
So, to do the comparison right you need to convert both given amount in dollars and calculated amount in whatever units you calculated it, to int or bigint quantities of cents.
If done correctly, the query will produce the correct result, and nobody goes to jail for nothing.
_____________
Code for TallyGenerator
August 31, 2016 at 9:27 am
Sergiy (8/31/2016)
But in this case they cannot be blamed.They did not create any mess.
Because there is no mess.
It's all done right, exactly according to the rules of fixed point computations.
I'll have to disagree with you there, ol' friend. Automatic truncation to a scale of 6 isn't correct. At least not in my book. If a dollar store calculator did such a thing, I'd take the time to get a refund. 😛
I'm an old man, and my memory cannot be trusted. :w00t:
But I can remember things which some people have forgotten, and some never heard of.
Amongst others, I can remember that when I studied FORTRAN in university (mid-80s) the issue with fixed point computations was exactly the same.
And there was no any element of surprise, or any attempt to put a blame on FORTRAN implementation, because before FORTRAN I studied "higher math" (as we named it), and questions of precision, truthful digits, systemic errors of different types of computations were a quite significant part of the curriculum.
I only dabbled in Fortran and that was back in the late 60's so can't say a thing there. I do remember that one had to be careful in other languages because, like it or not, none of them behave like a calculator but this nonsense of truncating to a scale of 6 or similar never reared it's ugly head. 15 digits of scale where always available even if they were sometimes displayed as only 13. 'bout the same as a 4 function calculator.
What we see is embedded deficiencies of fixed point computations.
Which make them so imprecise in most of cases, that they simply are not allowed in real science.
Totally agreed.
The idiotic statement from MS about precise decimals and approximate floats means only there is not a single person in MS who has a reasonable level of math education.
Or - if such person exists on their roster he/she does not have a say on what the publish in their documentation.
+ 1 million to that. Especially the genius that thought the "calculator" should automatically truncate to any scale other than the one that the calculator is fully capable of using.
The advantage of Granny's calculator is in the fact that it does 1 operation at the time, and after each single operation it converts result to the original scale-precision.
Absolutely agreed and that's what I implied in one of my previous posts. Not sure why MS decided to truncate to a scale of 6 any time the resulting precision went over the max of 38.
But still - with DECIMALs you'll never get the same level of precision as with FLOATs.
Certainly NOT in SQL Server/T-SQL because of the mistake they built in. Otherwise, I have to disagree. It's a bit ridiculous that in this day and age of modern computers and rather sophisticated math co-processors that MS decided to do something so stupid. Even worse, the same mistake has never been addressed throughout the decades long history of SQL Server.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 31, 2016 at 2:07 pm
Sergiy (8/30/2016)
Oh, gosh.No wonder our industry has a bad name.
Tom, here is something for you (and others) to think about.
And verify your instincts.
It's certainly indisputable that Microsoft (or was it the SQL Standards committees?) has completely screwed up fixed point arithmetic by having insane detemination of the precision and scale resulting from a calculation. But that isn't an inherent problem with fixed point decimal types, it's a problem arising from the tendency of non-mathematicians to screw up language design by insisting on wheels that have no curved surfaces (a tendency that has delivered plenty of other problems, not just this one). We had a perfectly good numeric type which adjusts the scale so that numbers will fit into their desginated amount of storage (float) and we used to have perfectly good fixed point decimal types (which didn't adjust scale, could optionally signal overflow and lost significance, and were closed under arithmetic operations except those leading to lost significance or to overflow) and then some database people who hadn't a clue about arithmetic or numerical methods came along and invented what is neither but combines the worst characteristics of each, and they called it "decimal" or "numeric" or "money".
I think my instincts are still reliable. I've done an awful lot of numerical stuff (scientific, engineering, and commercial) over the years. The longest calculation that occurs in the step from one line to the next in an amortization is a single multiplication (whose result has to be rounded appropriately before being stored and before being used) and decimal (even with the ludicrous scale adjustments) can beat float on precision for that (because multiplying the amount outstanding by the interest rate for the period since last balance calculation won't cause such rescaling unless something really weird is going on, like an interest rate not expressible in a small number of digits).
Tom
August 31, 2016 at 2:45 pm
I'll have to disagree with you there, ol' friend. Automatic truncation to a scale of 6 isn't correct. At least not in my book. If a dollar store calculator did such a thing, I'd take the time to get a refund.
You disagree because you believe in that truncation.
The fact is - there is no any truncation.
Change the decimal type in my scripts to (38, 35) and watch the precision dropped gradually, 4 digits at the time.
And there is no way around it. At in computer implementation.
You need to add 4 digits to prevent overflowing when multiplying 999.455*999.122, but you cannot just add more place holders.
38 is all you've got.
So you take those extra 4 digits from after the point.
It's described in every rule book of fixed point calculations on computers.
It happens because of limited bandwidth of any data storage in computers.
MS has nothing to do about it.
_____________
Code for TallyGenerator
August 31, 2016 at 3:23 pm
TomThomson (8/31/2016)
It's certainly indisputable that Microsoft (or was it the SQL Standards committees?) has completely screwed up fixed point arithmetic by having insane detemination of the precision and scale resulting from a calculation.
I guess you said enough to conclude - the fixed point computations as they exist in SQL Server are too imprecise (because of MS implementation or whatever other reason - does not matter) to be used in complex calculations involving more that 2 multiplications/divisions.
That confirms my point - for mortgage calculations (we're talking about T-SQL here, not Neverland where decimal computations implemented way better) we must use FLOAT, not DECIMAL.
Because FLOAT computations return results with significantly better precision.
_____________
Code for TallyGenerator
September 1, 2016 at 12:56 am
Sergiy (8/31/2016)
I'll have to disagree with you there, ol' friend. Automatic truncation to a scale of 6 isn't correct. At least not in my book. If a dollar store calculator did such a thing, I'd take the time to get a refund.
You disagree because you believe in that truncation.
The fact is - there is no any truncation.
Jeff, run this script:
DECLARE @D DECIMAL (38, 35), @F FLOAT
SET @D = 1.001 SET @F = 1.001
SELECT 0 [Number of Operations],
@D,
SQL_VARIANT_PROPERTY(@D, 'Precision') DecimalPrecision,
SQL_VARIANT_PROPERTY(@D, 'Scale') DecimalScale,
@F,
SQL_VARIANT_PROPERTY(@F, 'Precision') FloatPrecision,
SQL_VARIANT_PROPERTY(@F, 'Scale') FloatScale
UNION
SELECT 1 ,
@D*@D,
SQL_VARIANT_PROPERTY(@D*@D, 'Precision') DecimalPrecision,
SQL_VARIANT_PROPERTY(@D*@D, 'Scale') DecimalScale,
@F*@F,
SQL_VARIANT_PROPERTY(@F*@F, 'Precision') FloatPrecision,
SQL_VARIANT_PROPERTY(@F*@F, 'Scale') FloatScale
UNION
SELECT 2 ,
@D*@D*@D,
SQL_VARIANT_PROPERTY(@D*@D*@D, 'Precision') DecimalPrecision,
SQL_VARIANT_PROPERTY(@D*@D*@D, 'Scale') DecimalScale,
@F*@F*@F,
SQL_VARIANT_PROPERTY(@F*@F*@F, 'Precision') FloatPrecision,
SQL_VARIANT_PROPERTY(@F*@F*@F, 'Scale') FloatScale
UNION
SELECT 3 ,
@D*@D*@D*@D,
SQL_VARIANT_PROPERTY(@D*@D*@D*@D, 'Precision') DecimalPrecision,
SQL_VARIANT_PROPERTY(@D*@D*@D*@D, 'Scale') DecimalScale,
@F*@F*@F*@F,
SQL_VARIANT_PROPERTY(@F*@F*@F*@F, 'Precision') FloatPrecision,
SQL_VARIANT_PROPERTY(@F*@F*@F*@F, 'Scale') FloatScale
UNION
SELECT 4 ,
CONVERT(DECIMAL (38, 35), @D*@D*@D*@D*@D),
SQL_VARIANT_PROPERTY(@D*@D*@D*@D*@D, 'Precision') DecimalPrecision,
SQL_VARIANT_PROPERTY(@D*@D*@D*@D*@D, 'Scale') DecimalScale,
@F*@F*@F*@F*@F,
SQL_VARIANT_PROPERTY(@F*@F*@F*@F*@F, 'Precision') FloatPrecision,
SQL_VARIANT_PROPERTY(@F*@F*@F*@F*@F, 'Scale') FloatScale
UNION
SELECT 5 ,
CONVERT(DECIMAL (38, 35), @D*@D*@D*@D*@D*@D),
SQL_VARIANT_PROPERTY(@D*@D*@D*@D*@D*@D, 'Precision') DecimalPrecision,
SQL_VARIANT_PROPERTY(@D*@D*@D*@D*@D*@D, 'Scale') DecimalScale,
@F*@F*@F*@F*@F*@F,
SQL_VARIANT_PROPERTY(@F*@F*@F*@F*@F*@F, 'Precision') FloatPrecision,
SQL_VARIANT_PROPERTY(@F*@F*@F*@F*@F*@F, 'Scale') FloatScale
UNION
SELECT 6 ,
CONVERT(DECIMAL (38, 35), @D*@D*@D*@D*@D*@D*@D),
SQL_VARIANT_PROPERTY(@D*@D*@D*@D*@D*@D*@D, 'Precision') DecimalPrecision,
SQL_VARIANT_PROPERTY(@D*@D*@D*@D*@D*@D*@D, 'Scale') DecimalScale,
@F*@F*@F*@F*@F*@F*@F,
SQL_VARIANT_PROPERTY(@F*@F*@F*@F*@F*@F*@F, 'Precision') FloatPrecision,
SQL_VARIANT_PROPERTY(@F*@F*@F*@F*@F*@F*@F, 'Scale') FloatScale
See?
There is no any truncation to scale of 6.
Each of additional operation eats out another 4 digits from the scale.
Why 4?
Because the max number DECIMAL(38,35) can accommodate is 999.9(9)
Multiplying these 2 numbers gives 999999.9(9) - which after inevitable rounding (because the result exceeds 38 digits limitation) gives 1000000 - 7 digits.
So, to prevent arithmetic overflow of 2 decimal number multiplication we need to reserve for the result 7 digits:
(p1 - s1 ) + (p2 - s2) + 1 = (38-35) + (38-35) + 1 = 7
But we cannot increase the total precision of the number, because we are at its max.
So we have to reduce the number of significant digits after the decimal point:
38 - 7 = 31
That's the decimal scale you see after 1 operation in the script above.
2nd multiplication will remove other 4 digits, and so on, until only 6 "scale" digits left.
At this point, MS decided that they cannot go any lower in terms of decimal scale and allowed arithmetic overflow to happen rather than eliminate the decimal scale completely.
P.S. Granny's calculator is not any better than SQL Server in terms of decimal multiplication/division.
It only looks better because you don't see the whole picture.
Good calculators always have spare precision depth which is not shown on the display.
10 digit calculator usually holds 16 or 18 digits in memory, but displays only 10 of them.
If you run a test on a calculator having this in mind you pretty quickly will find out that calculators (except those scientific ones which do floating point computations) do decimal math with the same systematic errors as
SQL Server.
_____________
Code for TallyGenerator
September 17, 2017 at 10:49 pm
Best Loan Amoritzation schedule at below link
http://www.studycorners.com/make-emi-loan-sql-server-studycorners/
September 18, 2017 at 7:44 am
naveentiwari20 - Sunday, September 17, 2017 10:49 PMBest Loan Amoritzation schedule at below link
http://www.studycorners.com/make-emi-loan-sql-server-studycorners/
Heh.... "Best"? I was interested to see how that worked out. Then I saw the WHILE loop and wonder what else they missed out on. For example, did they remember that FLOAT only has 15 digits of precision and won't leave enough digits for scale at the beginning of larger loans? Did they handle penny rounding correctly (Granny and her spreadsheet love to-the-penny accuracy) We'll see...
--Jeff Moden
Change is inevitable... Change for the better is not.
September 18, 2017 at 8:27 am
Jeff Moden - Monday, September 18, 2017 7:44 AMnaveentiwari20 - Sunday, September 17, 2017 10:49 PMBest Loan Amoritzation schedule at below link
http://www.studycorners.com/make-emi-loan-sql-server-studycorners/Heh.... "Best"? I was interested to see how that worked out. Then I saw the WHILE loop and wonder what else they missed out on. For example, did they remember that FLOAT only has 15 digits of precision and won't leave enough digits for scale at the beginning of larger loans? Did they handle penny rounding correctly (Granny and her spreadsheet love to-the-penny accuracy) We'll see...
There are actually quite a few problems with that script. I posted comments on the page itself, but just in case they never make it out of "awaiting moderation", I will restate here:
Hello Naveen. There are several problems with your code here:
SELECT SUM(PrincipalPaid) AS [TotalPrincipalPaid] FROM #AmortizationSchedule;SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
September 18, 2017 at 9:32 am
Shifting to DECIMAL has it's own problems... like silent truncation to 6 decimal places in certain cases. I wish MS would figure out that systems do actually have math-coprocessors built into them and there hasn't been a need for decimal-place follies in software for a couple of decades now.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 61 through 75 (of 97 total)
You must be logged in to reply to this topic. Login to reply