Forum Discussion

PavolS's avatar
PavolS
Copper Contributor
Apr 01, 2025
Solved

Rounding issue - expected or bug?

Hello,

we use SQL Server 2019 and we got some unexpected rounding issues. See example:

It is only example with numbers, in our situation there were sub selects with data types:

0.4399 = decimal(20,4)

33.00 = decimal(20,2)

1 = smallint.

My question is, why in first and fifth selects is result only with 2 decimal and not 4 based on first number with data type decimal (20,4)? I have tested it with latest cumulative update.

Thank you for your answer. Pavol

  • The question is a bit complicated. It is related to the following topics in SQL Server:

    • The precision and scale for the constant value.
    • The precision and scale of the return value of SUM() function.
    • The result precision and scale of the operation(+, -, *, /, ...)
    • Data type precedence of the operators.

    A short version of the answer is: the SUM() function expend the precision of decimal expression to its maxinum, and keep its scale, that is decimal(38, s). This cause the result precision of the next operations over 38, the scale is reduced to provide enough space for the integer part. So the expressions contain SUM(decimal) will return lower scale 33.44.

    The full version

    Some facts

    • SUM() function returns the summation of all expression values in the most precise expression data type. To the decimal(p, s), it's decimal(38, s). https://learn.microsoft.com/en-us/sql/t-sql/functions/sum-transact-sql?view=sql-server-ver16#return-types
    • When an operator combines different data types, the data type with the lower precedence is first converted to the data type with the higher precedence. The precedence of decimal is higher than int. https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver16
    • When a constant int value is converted to the decimal, whose precision is just large enough to hold the value of the constant, for the value 1, it is converted to decimal(1,0). https://learn.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-ver16#conversion-and-parameterization
    • Assume 2 operand expressions e1 and e2, both are decimal type, with precision p1, p2 and scale s1, s2, they are e1: decimal(p1, s1) and e2: decimal(p2, s2)
      • The result precision of operation e1 * e2 is p1 + p2 + 1, and the scale is s1 + s2
      • The result precision of operation e1 + e2 is max(s1, s2) + max(p1 - s1, p2 - s2) + 1, and the scale is max(s1, s2).https://learn.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql?view=sql-server-ver16#remarks
    • The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, it's reduced to 38.
      • In addition operation( + ), when a result precision is greater than 38, the scale is reduced to min(precision, 38) - max(p1 - s1, p2 - s2).
      • In multiplication( * ), it need precision - scale places to store integer part. The scale reducing has some rules, one of them is "The scale isn't changed if it's less than 6 and if the integral part is greater than 32". 

    Back to your case, let's try to calculate the precision and scale of the first 2 lines. 
    All expression can be simplified to e1 + e2 * e3.

    The 1st line
    • e1: 0.4399, decimal(5, 4), p1 = 5, s1 =4 
    • e2: (select SUM(33.00)), decimal(38, 2), p2 = 38, s2 = 2
    • e3: 1, decimal(1, 0) p3 = 1, s3 =0
    • The precision of e2 * e3 = p2 + p3 + 1 = 38 + 1 + 1 = 40
    • The scale of e2 * e3 = s2 + s3 = 2+0 = 2
    • The precision is greater than 38, it is reduced to 38. The integer part is 40 - 2 = 38, and scale is 2, so the scale isn't changed.
    • The result of e2 * e3 is decimal(38, 2).
    • Consider e4 = e2 * e3, then p4 = 38, s4 =2, and e1 + e2 * e3 = e1 + e4 
    • The precision of e1 + e4 = max(s1, s4) + max(p1 - s1, p4 - s4) + 1 = max(4, 2) + max(5 - 4, 38 - 2) + 1 = 4 + 36 + 1 = 41
    • The precision is greater than 38,  it is reduced to 38, and the scale of e1 + e4 = min(precision, 38) - max(p1 - s1, p4 - s4) = min(41, 38) - max(5 - 4, 38 - 2) = 38 - 36 =2
    • So the result is decimal(38, 2), it show as 33.44
    2nd line
    • e1: 0.4399, decimal(5, 4), p1 = 5, s1 =4
    • e2: (select SUM(33)), int converted to decimal(2, 0), p2 = 2, s2 = 0
    • e3: 1, decimal(1, 0) p3 = 1, s3 =0
    • The precision of e2 * e3 = p2 + p3 + 1 = 2 + 1 + 1 = 4
    • The scale of e2 * e3 = s2 + s3 = 0 + 0 = 0
    • The result of e2 * e3 is decimal(4, 0).
    • Consider e4 = e2 * e3, then p4 = 4, s4 =0, and e1 + e2 * e3 = e1 + e4 
    • The precision of e1 + e4 = max(s1, s4) + max(p1 - s1, p4 - s4) + 1 = max(4, 0) + max(5 - 4, 4 - 0) + 1 = 4 + 4 + 1 = 9
    • The precision is less than 38, so scale of e1 + e4 =  max(s1, s4) = max(4 - 0) = 4
    • The result is decimal(9, 4), it show as 33.4399

    You can calculate the rest of lines using the rules. The key point is the data type and the precision of return value of SUM().

2 Replies

  • rodgerkong's avatar
    rodgerkong
    Iron Contributor

    The question is a bit complicated. It is related to the following topics in SQL Server:

    • The precision and scale for the constant value.
    • The precision and scale of the return value of SUM() function.
    • The result precision and scale of the operation(+, -, *, /, ...)
    • Data type precedence of the operators.

    A short version of the answer is: the SUM() function expend the precision of decimal expression to its maxinum, and keep its scale, that is decimal(38, s). This cause the result precision of the next operations over 38, the scale is reduced to provide enough space for the integer part. So the expressions contain SUM(decimal) will return lower scale 33.44.

    The full version

    Some facts

    • SUM() function returns the summation of all expression values in the most precise expression data type. To the decimal(p, s), it's decimal(38, s). https://learn.microsoft.com/en-us/sql/t-sql/functions/sum-transact-sql?view=sql-server-ver16#return-types
    • When an operator combines different data types, the data type with the lower precedence is first converted to the data type with the higher precedence. The precedence of decimal is higher than int. https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver16
    • When a constant int value is converted to the decimal, whose precision is just large enough to hold the value of the constant, for the value 1, it is converted to decimal(1,0). https://learn.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-ver16#conversion-and-parameterization
    • Assume 2 operand expressions e1 and e2, both are decimal type, with precision p1, p2 and scale s1, s2, they are e1: decimal(p1, s1) and e2: decimal(p2, s2)
      • The result precision of operation e1 * e2 is p1 + p2 + 1, and the scale is s1 + s2
      • The result precision of operation e1 + e2 is max(s1, s2) + max(p1 - s1, p2 - s2) + 1, and the scale is max(s1, s2).https://learn.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql?view=sql-server-ver16#remarks
    • The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, it's reduced to 38.
      • In addition operation( + ), when a result precision is greater than 38, the scale is reduced to min(precision, 38) - max(p1 - s1, p2 - s2).
      • In multiplication( * ), it need precision - scale places to store integer part. The scale reducing has some rules, one of them is "The scale isn't changed if it's less than 6 and if the integral part is greater than 32". 

    Back to your case, let's try to calculate the precision and scale of the first 2 lines. 
    All expression can be simplified to e1 + e2 * e3.

    The 1st line
    • e1: 0.4399, decimal(5, 4), p1 = 5, s1 =4 
    • e2: (select SUM(33.00)), decimal(38, 2), p2 = 38, s2 = 2
    • e3: 1, decimal(1, 0) p3 = 1, s3 =0
    • The precision of e2 * e3 = p2 + p3 + 1 = 38 + 1 + 1 = 40
    • The scale of e2 * e3 = s2 + s3 = 2+0 = 2
    • The precision is greater than 38, it is reduced to 38. The integer part is 40 - 2 = 38, and scale is 2, so the scale isn't changed.
    • The result of e2 * e3 is decimal(38, 2).
    • Consider e4 = e2 * e3, then p4 = 38, s4 =2, and e1 + e2 * e3 = e1 + e4 
    • The precision of e1 + e4 = max(s1, s4) + max(p1 - s1, p4 - s4) + 1 = max(4, 2) + max(5 - 4, 38 - 2) + 1 = 4 + 36 + 1 = 41
    • The precision is greater than 38,  it is reduced to 38, and the scale of e1 + e4 = min(precision, 38) - max(p1 - s1, p4 - s4) = min(41, 38) - max(5 - 4, 38 - 2) = 38 - 36 =2
    • So the result is decimal(38, 2), it show as 33.44
    2nd line
    • e1: 0.4399, decimal(5, 4), p1 = 5, s1 =4
    • e2: (select SUM(33)), int converted to decimal(2, 0), p2 = 2, s2 = 0
    • e3: 1, decimal(1, 0) p3 = 1, s3 =0
    • The precision of e2 * e3 = p2 + p3 + 1 = 2 + 1 + 1 = 4
    • The scale of e2 * e3 = s2 + s3 = 0 + 0 = 0
    • The result of e2 * e3 is decimal(4, 0).
    • Consider e4 = e2 * e3, then p4 = 4, s4 =0, and e1 + e2 * e3 = e1 + e4 
    • The precision of e1 + e4 = max(s1, s4) + max(p1 - s1, p4 - s4) + 1 = max(4, 0) + max(5 - 4, 4 - 0) + 1 = 4 + 4 + 1 = 9
    • The precision is less than 38, so scale of e1 + e4 =  max(s1, s4) = max(4 - 0) = 4
    • The result is decimal(9, 4), it show as 33.4399

    You can calculate the rest of lines using the rules. The key point is the data type and the precision of return value of SUM().

Resources