0

According to the documentation / (Division) (Transact-SQL) the result type is related to the Data Type Precedence, that said, in the following scenario, is there any way to receive the result type as a decimal(38, 10) ?

The calculation result seems loosing precision... A precise result should be 0.8123567224

declare @a decimal(38,10) = 0.8123567216;
declare @b decimal(38,10) = 0.9999999990;

select 
     [a] = @a
    ,[b] = @b
    , result1 = @a / @b
    , result2 = @a / CONVERT(decimal(38, 10), @b)
    , result3 = CONVERT(decimal(38, 10), @a) / CONVERT(decimal(38, 10), @b)
    , result4 = CONVERT(decimal(38, 10), (CONVERT(decimal(38, 10), @a) / CONVERT(decimal(38, 10), @b)))
marco13_457
  • 1
  • 1

1 Answers1

0

The rules for the resultant precision and scale for decimal division are noted in the table on this documentation page. This is the relevant table entry:

e1 / e2 p1 - s1 + s2 + max(6, s1 + p2 + 1) max(6, s1 + p2 + 1)

The doc excerpt:

In multiplication and division operations, we need precision - scale places to store the integral part of the result. The scale might be reduced using the following rules:

The resulting scale is reduced to min(scale, 38 - (precision-scale)) if the integral part is less than 32, because it can't be greater than 38 - (precision-scale). Result might be rounded in this case. The scale won't be changed if it's less than 6 and if the integral part is greater than 32. In this case, overflow error might be raised if it can't fit into decimal(38, scale) The scale will be set to 6 if it's greater than 6 and if the integral part is greater than 32. In this case, both integral part and scale would be reduced and resulting type is decimal(38,6). Result might be rounded to 6 decimal places or the overflow error will be thrown if the integral part can't fit into 32 digits.

This can be observed with sys.dm_exec_describe_first_result_set:

SELECT name, system_type_name
FROM sys.dm_exec_describe_first_result_set (N'
declare @a decimal(38,10) = 0.8123567216;
declare @b decimal(38,10) = 0.9999999990;

select 
     [a] = @a
    ,[b] = @b
    , result1 = @a / @b
    , result2 = @a / CONVERT(decimal(38, 10), @b)
    , result3 = CONVERT(decimal(38, 10), @a) / CONVERT(decimal(38, 10), @b)
    , result4 = CONVERT(decimal(38, 10), (CONVERT(decimal(38, 10), @a) / CONVERT(decimal(38, 10), @b)))
        ',NULL,NULL);
name system_type_name
a decimal(38,10)
b decimal(38,10)
result1 decimal(38,6)
result2 decimal(38,6)
result3 decimal(38,6)
result4 decimal(38,10)

If we reduce the integral part of the result3 operands from decimal(38, 10) to decimal(28, 10), the result type is the more precise decimal(38, 10), sufficient for the 0.8123567224 value:

select 
        [a] = @a
    ,[b] = @b
    , result1 = @a / @b
    , result2 = @a / CONVERT(decimal(38, 10), @b)
    , result3 = CONVERT(decimal(28, 10), @a) / CONVERT(decimal(28, 10), @b)
    , result4 = CONVERT(decimal(38, 10), (CONVERT(decimal(38, 10), @a) / CONVERT(decimal(38, 10), @b)));
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71