# Automatic decimal rounding issue

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!

-

## 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

• 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 http://stackoverflow.com/questions/423925/t-sql-decimal-division-accuracy/424052#424052

-
Alright, thanks for the info! The case in question is a data fix that's done on SQL Server side, to patch a client code bug, so doing this outside of SQL Server isn't feasible. But it's good to know my options. :) – Kahn May 8 '13 at 9:12
What do you mean "Don't do calculations in SQL language" where would you do them ? – IvanP May 9 '13 at 18:21
Well, @IvanP, I assume there's two choices. In the software code or by using CLR. The reality of course is that depending on the project and the surrounding constraints, we often do not have control over where a specific action is going to take place. But it's worth mentioning that some things are usually best left out of SQL Server in either case. – Kahn Jun 8 '13 at 9:16

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.

-