SQL Server为何截断decimal列计算值？如何避免该问题？

我编写了一个跨表计算的SQL查询用于向应用返回结果，经调试发现数值被意外截断的问题直接出在SQL Server中，而非应用端。

我有两张表，其中valued和pop_valued列均为decimal(38,9)类型：

select valued 
from dbname.dbo.TABLE_SUM
where market_id_2 = 103 -- 返回2454356.000000000

select pop_valued 
from dbname.dbo.TABLE_POP
where market_id_1 = 103 -- 返回8035229.000000000

用于计算的查询语句如下：

SELECT 
    sm.totalsortkey_bigid_0     AS totalsortkey_bigid_0,
    COALESCE(sm.market_id_2, 0) AS market_id_2,
    sm.advertiser_id_3          AS advertiser_id_3,
    sm.time_day_4               AS time_day_4,

    SUM(100.0 * (sm.valued /
          NULLIF(b.pop_valued, 0))) AS valued

FROM 
    dbname.dbo.TABLE_SUM sm
JOIN 
    dbname.dbo.TABLE_POP b ON sm.market_id_2 = b.market_id_1
                           AND sm.population_id_1 = b.population_id_0
GROUP BY 
    sm.totalsortkey_bigid_0,
    sm.market_id_2,
    sm.advertiser_id_3,
    sm.time_day_4

针对market_id=103，查询返回的valued为：30.544900
但使用相同数值执行测试查询：

select sum(100 * (2454356.000000000 / NULLIF(8035229.000000000, 0))) 

却能得到预期结果：30.544941531846821043

我曾参考类似问题尝试使用cast(val as decimal(38,9))，但未生效。请问如何修改查询以避免截断，得到精确的计算结果？

问题根源 #

1. 隐式类型转换丢失精度：原查询中使用100.0（float类型），导致decimal类型的除法结果被隐式转换为float进行乘法运算。float是近似数值类型，精度远低于decimal，会丢失部分小数位，最终SUM后的结果精度不足。
2. decimal除法默认精度限制：SQL Server中，两个decimal(38,9)类型相除时，默认计算出的结果小数位数有限，无法保留足够精度，后续运算进一步放大了截断问题。

修改方案 #

方案1：避免float隐式转换，提升运算精度 #

将100.0改为100（decimal类型），同时显式将参与除法的字段转换为更高精度的decimal类型（如decimal(38,18)），确保整个运算在高精度decimal下执行：

SELECT 
    sm.totalsortkey_bigid_0     AS totalsortkey_bigid_0,
    COALESCE(sm.market_id_2, 0) AS market_id_2,
    sm.advertiser_id_3          AS advertiser_id_3,
    sm.time_day_4               AS time_day_4,

    SUM(100 * (CAST(sm.valued AS decimal(38,18)) / NULLIF(CAST(b.pop_valued AS decimal(38,18)), 0))) AS valued

FROM 
    dbname.dbo.TABLE_SUM sm
JOIN 
    dbname.dbo.TABLE_POP b ON sm.market_id_2 = b.market_id_1
                           AND sm.population_id_1 = b.population_id_0
GROUP BY 
    sm.totalsortkey_bigid_0,
    sm.market_id_2,
    sm.advertiser_id_3,
    sm.time_day_4

方案2：简化转换（仅调整除法精度） #

如果确认100作为整数参与运算不会引发问题，也可以只提升除法部分的精度：

SUM(100 * (CAST(sm.valued AS decimal(38,18)) / NULLIF(b.pop_valued, 0))) AS valued

原理说明 #

• 将字段转为decimal(38,18)后，除法运算会生成更高小数位的结果（SQL Server会根据decimal运算规则自动分配足够的小数位数，最大可达38位），避免中间步骤的精度丢失。
• 使用100而非100.0，确保运算全程基于decimal类型，杜绝float近似转换带来的精度损失。

内容的提问来源于stack exchange，提问作者Fnr