A dilemma caught my attention not long ago. A very simple SELECT returned different values when a factor or a dividend/divisor changed order.
The first column (a) returns 0.012500 as expected but the second column (b) unexpectedly returns 0.012480. How come there is a difference?
Well, as it turns out, the answer is not straightforward. One part of the answer is about data type precedence, one part is about operator precedence and the last part is about precision, scale, and length.
Let us start with operator precedence where we learn that multiplication is carried out before division. So for column (a) the multiplication of factor 0.15 and factor 30 is carried out first and the intermediate result is 4.5. Later, 4.5 is divided by 360 for the result of 0.012500.
Column (b) is slightly different. In order to calculate the multiplication of factor 30 and the factor 0.15 divided by 360, SQL Server has to evaluate the division first, hence the intermediate result of the dividend 0.15 divided by 360 returns 0.00041(6). This intermediate factor result is then multiplied with 30 to give the final result of 0.012500. Wait!? What!? This is not what is happening in the original SELECT. Something else must be happening with the calculation.
Yes, and at this stage, it is all about precision and scale for the intermediate results.
For column (a) the factor 0.15 has a precision of 2 and scale of 2; (p ,s) equals (2, 2). This factor is then multiplied with factor 30, which SQL Server treats (p, s) as (2, 0) and not (10, 0) as expected for an INT, because of data type precedence described in the link above. The intermediate precision and scale is (5, 2) and the result of 0.15 * 30 is then 004.50. When SQL Server later divide by 360 (which is implicitly converted to a decimal with precision and scale of (3, 0) and not (10, 0) as expected for an INT), this gives the final (p, s) of (9, 6) and 004.50 divided by 360 is then 000.012500.
What happens with column (b)?
SQL Server first has to follow order of operators, which means the factor 0.15 / 360 must be calculated first. Dividend 0.15 has a (p, s) of (2, 2) and divisor 360 has a (p, s) of (3, 0), which gives the intermediate (p, s) of (6, 6) as described in the link above. The result of 0.15 / 360 is 0.00041666666666… which is truncated to fit in a (p, s) of (6, 6). Now we have the intermediate factor of 0.000416. This factor is then multiplied with 30 which has a (p, s) of (2, 0) for the final precision and scale of (9, 6), just as before! But 0.000416 multiplied with 30 is 000.012480.
And there is the full explanation of why the results differ in the SELECT query.