sql server - Why precision is decreasing when multiply sum to other number

Question

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.

Answer 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