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


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