14

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.

Victor Chekalin
  • 676
  • 1
  • 8
  • 15
  • Slightly off-topic (and it doesn't help your problem) but I believe `NUMERIC` should no longer be used in SQL Server - `DECIMAL` should take its place. – MarkD Jan 14 '13 at 06:07
  • Thnaks for advice. But with the decimal the same result – Victor Chekalin Jan 14 '13 at 06:12
  • I know :) Thats why I said `(and it doesn't help your problem)` – MarkD Jan 14 '13 at 06:19
  • Selecting into peristanttable from #tmp and instpectings resultdatatype will show a fieldtype on Numeric(38,6), SUM(Qnty*1.0) will lead to numeric(38, 9) – bummi Jan 14 '13 at 07:14

3 Answers3

8

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

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
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.

Gabe
  • 84,912
  • 12
  • 139
  • 238
0

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

Ingo
  • 1,805
  • 18
  • 31
  • I read that whole thread and didn't find anything in there that would explain the observed problem. – Gabe Jan 14 '13 at 07:47
  • then you havent read the explanations from Mat Henaire: ..., the result should be a numeric(77, 20). However, this is above SQL Server's maximum precision, so the result would need to be truncated. An implementation decision was made to sacrifice some scale to gain more digits on the left side of the decimal point, up to a minimum of scale 6. So the numeric(77,20) is actually converted to numeric(38, 6). The same behavior happens for division. That decision is based off the assumption that digits on the left side of the decimal point are usually more important than the ones on the right side. – Ingo Jan 14 '13 at 15:05
  • which again is what Mikael Eriksson explains in his answer above – Ingo Jan 14 '13 at 15:07
  • Why didn't you just post that in the first place (with a link to the source)? Unfortunately it still doesn't apply to the OP because his numbers are all `numeric(18,8)` rather than `numeric(38,10)`. – Gabe Jan 14 '13 at 17:14
  • It does apply, the maximum is still exceed when the operations occur. – jezza101 Feb 28 '17 at 11:58