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.

Answer 2

The answer lies in how computers represent numbers internally. Depending on the precision you use, SQL Server will allocate 5, 9, 13 or 17 bytes to represent your number (see http://msdn.microsoft.com/en-us/library/ms187746(v=SQL.90).aspx) So, for example when you moved from precision 30 to precision 20, the internal representation moved from 17 bytes to 13 bytes. How you set the scale on a 17 byte number versus a 13 byte number where a greater proportion of the number representation is dedicated to scale (15/30 = 0.5, 15/20 = 0.75, changes the rounding behavior. There is no perfect answer. The number types we have are good enough for most applications but sometimes you'll get strange artifacts like you're seeing due to the way we've compromised in representing numbers in computers.

As an aside, be very, very careful of float types. They only roughly approximate numbers and will give you very wrong results when used in quantity. They are superb for most scientific applications when no more than about 20 floating point numbers are used in one calculation. When used in quantity, say adding 1 million floating point numbers in a sum(column_name) you will get garbage. Demonstration below:

DECLARE @f FLOAT
DECLARE @n NUMERIC(20,10)
DECLARE @i INT

SET @f = 0
SET @n = 0
SET @i = 0

WHILE @i < 1000000
BEGIN
    SET @f = @f + 0.00000001
    SET @n = @n + 0.00000001
    SET @i = @i + 1
END

SELECT @n as [Numeric], @f as [Float]

This gives me the following answer on SQL Server 2008.

Numeric      Float
0.0100000000    0.00999999999994859