Menu
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.
635
Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2.
Precision is used for decimal. And length is the character length. nvarchar [ ( n | max ) ] Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000.
select length( substr( cast(Column as text), instr(cast(Column as text), '. ')+1 ) ) as "Column-precision" from "Table"; The code will cast the column as text, then get the index of a period ( . ) in the text, and fetch the substring from that point on to the end of the text.
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
50
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
