MSSQL - union all with different decimal precision

Question

When I execute this SQL

SELECT 1.4 UNION ALL
SELECT 2.0400 union all
SELECT 1.24

I get the following result:

1.4000
2.0400
1.2400

But when I execute the following SQL

SELECT sum(1.4) UNION ALL
SELECT sum(2.0400) union all
SELECT sum(1.24)

I get the following result:

1.4
2.0
1.2

Why is there a difference in what precision (scale) is applied for all records? Shouldn't it always use the precision where no data is loss, just like the 1st sql?

Thx.

Answer 1

I know that this is quite an old question, but none of the existing answers seem to address the "why?" aspect to your question.

First, what is the data type for your literal expressions? I wasn't sure (and didn't look it up) so I ran the following:

select 1.4 union all
select 'frob'

which returns the error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Okay, so 1.4 and the other literals are numeric - a.k.a decimal.

Next, what is the return type of the SUM function, if passed a decimal(p,s)¹:

decimal(38, s)

Okay, so the data types of the 3 SUM expressions in your query are decimal(38,1), decimal(38,4) and decimal(38,2). Given those 3 data types available to pick from, decimal(38,1) is the final chosen type, based on the rules for differing precisions and scales.

The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

So, finally, back to the documentation on decimal:

By default, SQL Server uses rounding when converting a number to a decimal or numeric value with a lower precision and scale. However, if the SET ARITHABORT option is ON, SQL Server raises an error when overflow occurs. Loss of only precision and scale is not sufficient to raise an error.

So that's your final result.

---

¹ At first this type may seem surprising, until you realise that generally, sum will operate against multiple rows and it's easily possible for multiple values of a given precision and scale to overflow their own data type. decimal(38,s) gives the largest possible space to accommodate any overflows without losing any precision, for a particular SUM() occurrence, and means that the final data type can be decided upon before the query has executed.