Why precision is decreasing when multiply sum to o

问题:

I have encountered with following bug (or feature) in SQL Server.

When I use SUM (*column*) where column has a numeric(18, 8) type and multiply it to any other number (integer or decimal) the result precision is reducing to numeric(18, 6).

Here is the example script to demonstrate.

CREATE TABLE #temp (Qnty numeric(18,8))

INSERT INTO #temp (Qnty) VALUES (0.00000001)
INSERT INTO #temp (Qnty) VALUES (0.00000002)
INSERT INTO #temp (Qnty) VALUES (0.00000003)

SELECT Qnty, 1*Qnty
FROM #temp

SELECT (-1)*SUM(Qnty), SUM(Qnty), -SUM(Qnty), SUM(Qnty) * CAST(2.234 as numeric(18,8))
FROM #temp

DROP TABLE #temp

The result of second SELECT query

0.000000    0.00000006  -0.00000006 0.000000

As you can see then I multiply SUM the result is 0.000000

Could anyone explain the strange behavior?

UPD. I executed this query in SQL Management Studio on 2000, 2005 and 2008 SQL Server.

回答1:

Aggregating a numeric(18, 8) with SUM results in the datatype numeric(38, 8).

How the resulting datatype is calculated when multiplying something with numeric can be found here: Precision, Scale, and Length (Transact-SQL)

The datatype for your constant -1 is numeric(1, 0)

Precision is p1 + p2 + 1 = 40
Scale is s1 + s2 = 8

Max precision is 38 and that leaves you with numeric(38, 6).

Read more about why it is numeric(38, 6) here: Multiplication and Division with Numerics

回答2:

If you read SUM's reference page, you'll see that on a decimal column it yields a type of NUMERIC(38,6). You need to cast the result of the SUM to NUMERIC(18,8) for it to work the way you want.

Executing SELECT CAST(SUM(Qnty) as numeric(18,8)) * 2.234 FROM #temp yields 0.00000013404 as you'd expect.

回答3:

a technical explanation can be found at http://social.msdn.microsoft.com/Forums/en/transactsql/thread/233f7380-3f19-4836-b224-9f665b852406