The question is relatively simple. I need to calculate 3 columns where the mid results are huge decimals, and I'm running into a problem early on with SQL Server basically rounding the decimals regardless of any cast / converts.

For example, let's do a simple division as 1234/1233. A calculator will produce 1,00081103000811. But when I do this on SQL Server, we get the following:

-- Result: rounded at 1.000811000... with trailing zeroes up until the 37 precision
SELECT CAST(CAST(1234 AS DEC(38,34))/CAST(1233 AS DEC(38,34)) AS DEC(38,37))

-- Result: rounded at 1.000811
SELECT CONVERT(DECIMAL(38,32), 1234)/CONVERT(DECIMAL(38,32),1233)

-- Correct result at 1,00081103000811
-- But this requires the zeroes to be put in manually when you don't
-- even know the precision of the end result
SELECT 1234.0/1233.00000000000000

Why does this automatic rounding occur? And what's the best way to calculate insanely long decimal values when you can't be sure how big a number (the int or dec part) will be, since the table can contain various different values?

Thanks!

up vote 16 down vote accepted

tl;dr

Don't do calculations in SQL language

Longer

The result scale and precision is well defined here on MSDN. It isn't intuitive, really. However, in simple terms, precision is lost when the input scales are high because the result scales need to be dropped to 38 with a matching precision drop.

To confirm things

  • Your extra CAST in the first example simply add zeroes
  • The truncation happens as per my MSDN link (2nd example)
  • The 3rd example with constants has implied decimal values that are just enough (5,1) and 18,14).
    This means the result scale and precision have no truncation (see blow)

More on the 1st and 3rd cases..

The result scale for a division is max(6, s1 + p2 + 1):

  • First example, this is 77 which is dropped to 38. Precision is forced down similarly, subject to a minimum of 6 (see this)
  • Third example, this is 24 so precision does not need adjusted

You have some options

  • calculate in the client code eg .net
  • use CLR functions to do .net calculations
  • live with the loss of accuracy
  • use float and live with 15 significant figures as best

FInally, see this on SO https://stackoverflow.com/questions/423925/t-sql-decimal-division-accuracy/424052#424052

Not sure if this helps but for me my temp table column was set to decimal, I was passing convert(decimal(15,2), 0.65) from the insert into temp_table. This was auto rounding, I changed the column type to decimal(16,2) to match what was being passed. Table is now storing 0.65.

I had to do a work around. Here's what I did:

-----------------------------------
DECLARE @DENIED INT  = 33443
DECLARE @PAID INT = 148353
DECLARE @PCT Decimal (6,2)

SET @PCT = (@DENIED * 100.00 / @PAID)  -- Instead of dividing by 100, I included decimals

SELECT
@DENIED AS DEN
,@PAID AS PAID
,@PCT AS PCT

Results:

DEN PAID    PCT
-----   ----    -----
33443   148353  22.54   -- Instead of 22.00

Hope this helps.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.