tsql - Sql Server Precision Crazyness

Question

I am having an issue with sql server precision.

I have the following queries:

DECLARE @A numeric(30,10)
DECLARE @B numeric(30,10)
SET @A = 20.225
SET @B = 53.3875
SELECT @A * @B

DECLARE @A1 numeric(30,14)
DECLARE @B1 numeric(30,14)
SET @A1 = 20.225
SET @B1 = 53.3875
SELECT @A1 * @B1

DECLARE @A3 numeric(30,15)
DECLARE @B3 numeric(30,15)
SET @A3 = 20.225
SET @B3 = 53.3875
SELECT @A3 * @B3

DECLARE @A2 numeric(20,15)
DECLARE @B2 numeric(20,15)
SET @A2 = 20.225
SET @B2 = 53.3875
SELECT @A2 * @B2

DECLARE @A4 float
DECLARE @B4 float
SET @A4 = 20.225
SET @B4 = 53.3875
SELECT @A4 * @B4

Which yields the following results respectively:

1079.762188

1079.762188

1079.7621875

1079.762187500000000000000000000

1079.7621875

The correct answer is: 1079.7621875.

I do not understand why, when the types have the same signature they are losing precision. Also, why does going from 30,14 to 30,15 fix the precision problem? Also, why does 20,15 have so many more decimals than 30,15?

I have read this article http://msdn.microsoft.com/en-us/library/ms190476(SQL.90).aspx and I think I should be fine because my variables have the same precision.

Any help would be much appreciated!

Answer 1

This is important at the bottom of the link that you pasted:

• The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

For all of the results where you have a precision of 30, the resultant calculated precision is 61. Since the maximum precision possible is 38 the resultant precision is being reduced by 23. Thus, all of the scales are being reduced as well to avoid truncating the integral parts of the result any more than absolutely necessary.

The 2nd to last value, where the precision of each value is 20, the resultant precision is 41, which only needs to be reduced by 3, leaving a might lighter reduction in the scale portion.

(30,15) works because the resultant scale is 30, so, when it gets reduced it's still large enough to hold the value you want.

Lesson: Don't make precision and scale any large than you need them to be, or you'll get odd results.