Why does SQL Server override my scale value when I am casting from VARCHAR(MAX) to Numeric(P,S)?-CodePudding

I am trying to understand why SQL Server is modifying my scale value when I cast a value from VARCHAR(MAX) to NUMERIC(P,S).

Here is the example:

--declare @val1 as numeric (18,6)
--declare @val2 as numeric (18,6)

DECLARE @val1 AS VARCHAR(MAX)
DECLARE @val2 AS VARCHAR(MAX)

SET @val1 = '2383.4912500000'
SET @val2 = '2383.4912000000'

DROP TABLE IF EXISTS TMP

SELECT
    V1 = @VAL1, 
    VC1 = CAST(@VAL1 AS NUMERIC(30, 20)),
    valDiff = ABS(CAST(CAST(@VAL2 AS NUMERIC(18, 6)) - CAST(@VAL1 AS NUMERIC(18, 6)) AS NUMERIC(30, 20)) / @VAL1) 
INTO  
    TMP

SELECT * FROM TMP

-- when variable is VARCHAR(MAX), valDiff == 0.00000002 which is correct, but rounded.

-- when the variable is NUMERIC(18, 6), valDiff == 0.0000000209776310275945 which is correct and not rounded

CodePudding user response：

When you start with decimals with very high precision, the results of arithmetic operations may have their scale reduced to preserve more significant digits. It doesn't matter if the values dont have 30 non-zero digits to the left of the decimal point. The conversions are based on the declared precision and scale:

Precision and Scale

And here your varchar is going through an implicit conversion to a decimal with excessive precision.

Because of the need to carefully cast intermediate results with decimal, it's often easier to perform all the calculations using float, which maintains ~15 significant digits of precision across all scales, and only cast the final result to decimal for storage. eg

declare @val1 as float ='2383.4912500000'
declare @val2 as float ='2383.4912000000'

select cast( abs(@VAL2 - @VAL1) / @VAL1 as numeric(30,20) )