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?