SQL Server中decimal除法精度丢失原因探究：不同精度与类型结果差异

Great question—this boils down to how SQL Server calculates precision and scale for decimal/numeric arithmetic operations, especially division. Let’s break down your three scenarios to see exactly what’s happening:

1. Why decimal(38,10) returns 0 #

SQL Server uses strict rules to determine the precision (total number of digits) and scale (digits after the decimal point) of results from decimal operations. For division (a / b), the formula for the result's scale is:
max(6, s1 + p2 + 1)
Where:

• s1 = scale of the dividend (@base, 10 in this case)
• p2 = precision of the divisor (@div, 38 here)

Plugging in your values: max(6, 10 + 38 + 1) = 49. The result's precision is calculated as p1 - s1 + s2 + scale_result, which gives 38 - 10 + 10 + 49 = 87.

But here’s the catch: SQL Server’s maximum precision for decimal types is 38. When the calculated precision exceeds 38, it truncates the result to fit, which means reducing the scale (decimal places) to keep the total digit count at 38. In this case, the integer part of the division result would need 38 digits (from the precision calculation), leaving 0 digits for the decimal part.

So @base / @div gets rounded to 0, and multiplying 0 by @mult naturally gives 0.

2. Why decimal(28,10) returns the correct non-zero value #

Using decimal(28,10) changes the calculation:

• Scale of the division result is still max(6, 10 + 28 + 1) = 39
• Calculated precision is 28 - 10 + 10 + 39 = 67, which still exceeds 38.

But this time, when truncating to 38 total digits, there’s enough room to retain decimal places. The integer part of the division result is 0 (since @base is much smaller than @div), so all 38 digits can be used for decimal places. This preserves enough precision to represent the small quotient accurately, and multiplying by @mult gives the expected non-zero result.

3. Why float matches Excel/Wolfram Alpha #

float in SQL Server is an IEEE 754 double-precision floating-point type, which uses a different storage mechanism than decimal. It prioritizes representing a wide range of values with ~15-17 significant digits, rather than exact decimal precision. Excel and Wolfram Alpha also use this floating-point standard for calculations, so their results align with SQL Server’s float output. Note that float is approximate—you might see tiny rounding differences with very large/small numbers, but it works well for your use case.

Key Takeaway #

Decimal types in SQL Server are exact, but their arithmetic operations are bound by strict precision/scale rules that can lead to unexpected truncation when working with very large precision values. If you need to avoid this, consider:

• Using a smaller precision (like your decimal(28,10) example) where possible
• Explicitly casting intermediate results to a decimal type with sufficient scale
• Using float when exact decimal precision isn’t critical and you need alignment with other tools like Excel

内容的提问来源于stack exchange，提问作者Gabriel Guimarães