Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
add comment

1 Answer

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

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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