January 27, 2020 at 12:00 am
Comments posted to this topic are about the item Auto Rounding
January 27, 2020 at 5:49 am
Nice question to start the week on, thanks Steve
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
January 27, 2020 at 8:52 am
Nice question, which required a fair amount of reading and calculating back and forth.
But there is a small issue with the "correct" answer: There is one zero missing in the first result. It should have 6 zeros, since the result has the type DECIMAL(38, 6), but it is displayed with only 5 zeros.
January 27, 2020 at 12:08 pm
I'll do a little editing on the answer.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 27, 2020 at 2:38 pm
This is a very good question which clearly demonstrates the consequences of failing to pack the precision in order to avoid any unwanted rounding. It might be tempting to declare the decimal variables as luxuriously as possible (such as 38, 12 in the first snippet), but this is the root cause of the problem, not the rounding, which takes place only because the engine has no choice.
Using the info in documentation, the first example uses decimal(38, 12), so to calculate the precision and scale the following is used (p = p1 + p2 + 1 ans s = s1 + s2) : precision = 38 + 38 + 1 = 77, and s = 12 + 12 = 24. Because 77 is higher than the absolute max of 38 for decimal data type, the result is adjusted using the following rule: if the integral part (to store the numbers before the decimal point) is greater than 32 (yes, 77 - 24 = 53 is greater than 32), and the scale is greater than 6 (yes, 24 is greater than 6) then both precision and scale are reduced to 38 and 6 respectively. This means that the resulting data type for the first snippet is decimal(38, 6), so the engine has no choice but to round the result to 6 digits.
The second snippet uses a more reasonable decimal(20, 12). The preliminary p and s for the result are 20 + 20 + 1 = 41 and the scale is still 12 + 12 = 24. The precision is greater than absolute maximum of 38, but the good news is that the integral part is smaller than 32 (41 - 24 = 17). Thus, the precision is reduced to 38 and the scale is reduced based on the following formula: min(s, 38 - (p - s)), so min(24, 38 - 17) = min(24, 21) = 21. The final result is decimal(38, 21). These 21 scale digits are more than enough to accommodate the correct result of multiplication without the need of any rounding.
Finally, if the literals are used, i.e.
select 0.9999999 * 0.9999999 result;
the result is 0.99999980000001 without any extra zeroes. The literal 0.9999999 is evaluated as decimal(8, 7), so the final result is evaluated to decimal(17, 14) because p = 8 + 8 + 1 = 17 and s = 7 + 7 = 14, and there is nothing to adjust. There are no any extra zeroes either.
When dealing with precision packing, which is an absolute must if the unwanted rounding is to be avoided if possible, it is a good idea to remember that aggregates might reintroduce the dreaded precision 38 implicitly. For example,
;with records as (
select n from (values(cast(0.999999 as decimal(16, 6)))) t(n)
)
select n * 0.001 result from records;
The result is 0.000999999 as expected. Should I change the final select to read select sum(n) * 0.001 result from records, the result is 0.001000 even though the source "table" still has only one row, one column and the data type of that column is still decimal(16, 6). What happens here is this: The result of the sum of decimal(p, s) is always decimal(38, s) because the engine has no choice but to use the maximum allowed precision when summing up the unknown number of numbers. In this case, the sum(n) is evaluated as decimal(38, 6) and the literal 0.001 as decimal(4, 3). The resulting precision is greater than 38, so it is forced to be 38, and scale is 6. Thus, the preliminary result of 0.000999999 needs to be rounded to 6 digits, resulting in 0.001000.
Oleg
January 28, 2020 at 2:59 am
Oleg - What an awesome badass analysis of the problem, love it!
January 28, 2020 at 3:58 pm
Testing, I get:
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@9S".
Msg 137, Level 15, State 2, Line 5
Must declare the scalar variable "@9S".
Casing matters! 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply