SQL formula returns inconsistent precision

Question

The result of the two queries should be identical. The same data. The same formula. The same cast. One result is computed in a query against a table variable, and the second for variables. I replaced the table variable with a temporary table and a constant table with the same 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 

The same results against different servers. SQL Server 2008 R2 Web Standard, Standard and Express, and SQL Server 2012 Standard.

Answer 1

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

Change @comm to (38,6) :

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

I get:

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

Similarly, changing comm in @t to [comm] [decimal](20 , 6) , I get:

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

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

Answer 2

@comm defined as decimal (20.6), and the comm column is decimal (38.6). You also assign a value with 7 decimal points to @comm, which only accepts up to 6 decimal places

According to docs , decimal numbers with an accuracy between 20-28 occupy 13 bytes, and large decimal numbers - 17 bytes. When you SELECT larger value stored in comm back to the smaller @comm variable, some rounding will occur.

Answer 3

It sounds like the difference between the implicit clicks of your data types on the output type between the select fields using and the selection using variables in memory.

When performing mathematical operations on variables / fields of different data types, it is recommended that CAST first display the (or the same intermediate) data type.

In this case, your problem may be due to DECLARE @comm DECIMAL(20 , 6) , which is different from your output type DECIMAL(32, 10)