Re: Multiplying numeric(19,4) Values

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Dear Hugo,

Greetings to you, long time no speak :)

Ah ha, I certainly see now why the query has been exploding: I had
misread the purpose of the NUMERIC_ROUNDABORT server options and had
reversed their true meaning in my mind. They are correctly set to
TRUE so that is why I am getting the error.

I have read your explanation and I see that it makes (as usual)
perfect sense to me now in understanding how SQL Server determines the
maximum data types when combining datatypes through multiplication. I
guess it is true, you really do learn somethign new every single day.

Best wishes,

Alex

On Sep 18, 4:30 pm, Hugo Kornelis
<h...@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Hi Alex,

Thanks for providing the repro code.

(snip)





create table #test (
 id int identity(1,1) not null
, baldata numeric(13,4) not null
, pricedata numeric(10,4) not null
, valuedata numeric(10,6) not null
, currencydata numeric(14,8) not null
)
insert #test (baldata, pricedata, valuedata, currencydata) values
(123456789.1234, 10451.695, 1.043544, 149.91567605)

THESE WORKS:
select *
,(baldata + pricedata + valuedata + currencydata) as tcalc
from #test
select *
,(baldata * pricedata * valuedata) as tcalc
from #test

THIS DOESNT WORK:
select *
,CAST(baldata + pricedata + valuedata + currencydata AS numeric(19,4))
as tcalc
from #test

THESE DONT WORK:
select *
,(baldata * pricedata * valuedata * currencydata) as tcalc
from #test
select *
,CAST(baldata * pricedata * valuedata * currencydata AS numeric(19,4))
as tcalc
from #test

The reason for this is how SQL Server handles precision for numeric
data. The ultimate goal is not to lose any precision. If you multiply
two numbers, and the first has 5 digits before the decimal place and the
other 3, the maximum result has (5+3=)8 digits before the decimal. (As
the maximum result is 99,999*999=99,899,001). A similar rule goes for
the digits after the decimal pace - if one number has two and the other
3, the result will have at most (2+3=)5 digits after the decimal.

So the maximum precision of the multiplication numeric(a,b)*numeric(c,d)
is numeric(a+c,b+d). And that's the base rule SQL Server applies -
except that for some reason I don't understand, one extra digit is used
so the actual result is numeric(a+c+1,b+d)

So, let's start at two:
baldata * pricedata --> numeric(13,4) * numeric(10,4) = numeric(24,8)

Add the third factor:
(baldata * pricedata) * valuedata --> (numeric(13,4) * numeric(10,4) ) *
numeric(10,6) = numeric(24,8) * numeric(10,6) = numeric(35,14).

So far no problems. All within SQL Server's allowed range. But now, we
add the fourth factor:
(the above) * currencydata --> numeric(35,14) * numeric(14,8) =
numeric(50,22).

Now we have a problem. The maximum precision is 38, and 50 is way above
that number. Books Online says that in this case, the scale is reduced
so that precision behind the decimal may be lost in favor of keeping the
integral part. That means you run the risk of losing precision. And that
is where the setting of NUMERIC_ROUNDABORT and ARITHABORT come in.

- NUMERIC_ROUNDABORT OFF: The result is rounded (if needed) and no
errors and warnings are generated.
- NUMERIC_ROUNDABORT ON and ARITHABORT OFF: A warning is generated and
NULLS are returned.
- NUMERIC_ROUNDABORT ON and ARITHABORT OFF: An error is generated and no
result set is returned.

If you add the statement SET NUMERIC_ROUNDABORT OFF at the start of your
script, you'll see that it runs fine.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis- Hide quoted text -

- Show quoted text -

.

Relevant Pages

  • Re: I want to KEEP trailing zeros
    ... take a look at the decimal and numeric data types in SQL Server ... You can specify the precision (total number of digits the ... technicians need to record numbers with varying scale and precision. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: ado.net and sql 2005
    ... Our framework generates a set of business objects and maps them through a ... .Net 1.1 decimal type and a sql server table column of money. ... about exceeding the precision but I was under the assumption for a money ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Multiplying numeric(19,4) Values
    ... , valuedata numericnot null ... The reason for this is how SQL Server handles precision for numeric ... The ultimate goal is not to lose any precision. ... and the first has 5 digits before the decimal place and the ...
    (microsoft.public.sqlserver.programming)
  • Re: use of tempdb by union all
    ... I trust that both you and Andrew recognize that precision in ... there are special situation where space inside the user database are ... > least one special situation where SQL Server will use space inside the ... >> Chief Tenaya ...
    (microsoft.public.sqlserver.programming)
  • Re: Saving a number with high preciosion in a ADO.NET table
    ... if it sufficient to only specify the decimal or I must add the precision as ... Sql Server is float, not decimal. ... that you specify. ...
    (microsoft.public.dotnet.languages.csharp)