CODEWITHSUNDEEP
sqlsql-servert-sql
Crispy Ninja
Crispy Ninja

Reputation: 348

SQL Formula Returns Inconsistent Precision

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.

Upvotes: 4

Views: 213

Answers (3)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131423

@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.

Upvotes: 4

Monty Wild
Monty Wild

Reputation: 3991

It sounds as if there is a difference in the implicit casts of your data types to the output data type between a select using fields and a select using in-memory variables.

When performing mathematical operations on variables/fields of differing data types, it is advisable to CAST them to the output (or the same intermediate) data type first.

In this case, your problem may be because of the DECLARE @comm DECIMAL(20 , 6), which has a different precision to your output type of DECIMAL(32, 10)

Upvotes: 0

Hart CO
Hart CO

Reputation: 34774

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.

Upvotes: 5

Related Questions