SQL Formula Returns Inconsistent Precision

Question

The result of the two queries should be identical. Same data. Same formula. Same cast. One result is calculated in a query against a table variable, while the second is calculated against variables. I have replaced the table variable with temp table and permanent table with identical results.

Why are my results different?

DECLARE
    @comm DECIMAL(20 , 6)
  , @quantity INT
  , @multiplier INT
  , @price DECIMAL(38 , 10)

SET @comm = 210519.749988;
SET @quantity = 360000;
SET @multiplier = 1;
SET @price = 167.0791666666;

DECLARE @t AS TABLE
    (
      [comm] [decimal](38 , 6)
    , [multiplier] [int]
    , [Quantity] [int]
    , [Price] [decimal](38 , 10)
    )

INSERT INTO @t
    VALUES
        ( @comm , @quantity , @multiplier , @price )

SELECT
        @comm = comm
      , @quantity = quantity
      , @multiplier = multiplier
      , @price = price
    FROM
        @t

SELECT
        CAST(comm / quantity / multiplier / price AS DECIMAL(32 , 10))
    FROM
        @t
UNION ALL
SELECT
        CAST(@comm / @quantity / @multiplier / @price AS DECIMAL(32 , 10));

Result

1. 0.0034990000
2. 0.0035000000

Same results against different servers. SQL Server 2008 R2 Web Edition, Standard and Express and SQL Server 2012 Standard.

Answer 1

The difference is due to the difference in precision of your two DECIMAL fields:

Changing @comm to (38,6):

DECLARE
    @comm DECIMAL(38 , 6)
  , @quantity INT
  , @multiplier INT
  , @price DECIMAL(38 , 10)

I get:

---------------------------------------
0.0034990000
0.0034990000

Likewise changing comm in @t to [comm] [decimal](20 , 6) gets me:

---------------------------------------
0.0035000000
0.0035000000

If the fields are consistent, the results will be consistent.

Answer 2

@comm is defined as decimal(20,6) while the comm column is decimal(38,6). You also assign a value with 7 decimal points to @comm, which only accepts up to 6 decimals

According to the docs, decimals with a precision between 20-28 take 13 bytes while larger decimals use 17 bytes. When you SELECT the larger value stored in comm back into the smaller @commvariable some rounding is bound to happen.