Answered by:
Stop SQL server from rounding precise calculations

Hi,
I'm trying to caluclate some very extact figures using a fairly complicated algorithm and I'm getting wild variances in the output. I've tracked it down and it would seem SQL server rounds when ever it feels like it.
Here is a simplified version of my problem...
DECLARE
@A DECIMAL(38,17), @B DECIMAL(38,17)
SELECT
@A = 55.5147203733824, @B = 53.2679199504953
SELECT
A, B, A  B AS c
, (AB)/ B AS PC
FROM
(SELECT @A AS A, @B AS B
)
x
As you can see PC has been round down considerably. If I explicitly tell SQL server to use more memory for the calc I still don't get the precision I've requested.
eg.
,(CAST( A AS DECIMAL(38,17))  CAST( B AS DECIMAL(38,17))) / CAST( B AS DECIMAL(38,17)) AS PC_CAST
Casting the input has no effect at all.
Thanks,
Gary
Question
Answers

I've tracked it down and it would seem SQL server rounds when ever it feels like it.
Here is a simplified version of my problem...
DECLARE
@A DECIMAL(38,17), @B DECIMAL(38,17)
You are pushing SQL Server to the limit, and forcing it to round.
If you multiply two numbers of 2 digits, then the end result could take up to 4 digits. If you multiply two number of 3 digits, the end result could take up to 6 digits.
Similar rules apply when you divide number, etc.
The maximum precision of decimal is 38 digits, and you have declared the variables as such. This means that all room to "grow" the end result in precision is made impossible. If I change the definition of @A and @B to DECIMAL(15,13) then I get this result:
A B c PC
   
55.5147203733824 53.2679199504953 2.2468004228871 .0421792408071344002616
Are those the results you were looking for?
Look up the topic "Precision, Scale, and Length (TransactSQL)" in BOL. It will tell you exactly how precision and scale determine the maximum precision and scale after performing mathematical operations.

GertJan
 Marked as answer by KJian_ Monday, July 12, 2010 6:03 AM
All replies



I've tracked it down and it would seem SQL server rounds when ever it feels like it.
Here is a simplified version of my problem...
DECLARE
@A DECIMAL(38,17), @B DECIMAL(38,17)
You are pushing SQL Server to the limit, and forcing it to round.
If you multiply two numbers of 2 digits, then the end result could take up to 4 digits. If you multiply two number of 3 digits, the end result could take up to 6 digits.
Similar rules apply when you divide number, etc.
The maximum precision of decimal is 38 digits, and you have declared the variables as such. This means that all room to "grow" the end result in precision is made impossible. If I change the definition of @A and @B to DECIMAL(15,13) then I get this result:
A B c PC
   
55.5147203733824 53.2679199504953 2.2468004228871 .0421792408071344002616
Are those the results you were looking for?
Look up the topic "Precision, Scale, and Length (TransactSQL)" in BOL. It will tell you exactly how precision and scale determine the maximum precision and scale after performing mathematical operations.

GertJan
 Marked as answer by KJian_ Monday, July 12, 2010 6:03 AM