DECIMAL( Precision,Scale) to get maximum accuracy in calculations

I divided 500/15 and then multiply the result back with 15 but I get different results on the different Precision and Scale.

Please run the following script.

DECLARE @cost DECIMAL(36,4) 
SET @cost = 500
SELECT @cost/15, (@cost/15) * 15
-- Scale is 4 so on multlyping, after dividing, result is not 500
-- Result = 33.333333, 499.999995

DECLARE @cost2 DECIMAL(36,5)
SET @cost2 = 500
SELECT @cost2/15, (@cost2/15) * 15
-- Scale is 5 so on multlyping, after dividing, result is 500
-- Result = 33.3333333, 500.000000

DECLARE @cost3 DECI开发者_C百科MAL(32,5)
SET @cost3 = 500
SELECT @cost3/15, (@cost3/15) * 15
-- Precision decrease to 32 from 36 result is again not 500
-- Result = 33.33333333, 499.99999995

I need to store this calculation in db and I need maximum accuracy. This is not some kind of scientfic application but client need to have this.

What I want to know that, I need the accuracy which gives me Decimal(36, 5). Should I use this becasue it need 17 bytes? What should be the better approach to get accuracy which I have explained in examples?

The problem with the conversion is that DECIMAL(36, 4) is technically not adequate to store the intermediate result of the division of 500 by 15. SQL Server correctly estimates it as a FLOAT.

The correct way of doing it would be to multiply first and then divide.

DECLARE @cost DECIMAL(36,4) 
SET @cost = 500
SELECT @cost/15, @cost*15/15
-- Scale is 4 so on multlyping, after dividing, result is not 500
-- Result = 33.333333, 500