title: Why does SQL Server override my scale value when I am casting from VARCHAR(MAX) to Numeric(P,S)?
body: <p>I am trying to understand why SQL Server is modifying my scale value when I cast a value from <code>VARCHAR(MAX)</code> to <code>NUMERIC(P,S)</code>.</p>
  <p>Here is the example:</p>
  <pre><code>--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
  </code></pre>