SQL precision arithmetic overflow error for numeric and decimal datatypes

[Tariq]

I've a SQL view performing a number of very precise calculations ie.

495/(1.112 - (.00043499*((Co l1 + Col2 + Col3))) + ( .00000055 * ((Col1
+ Col2 + Col3)) * ((Col1 + Col2 + Col3))) - (.00028826 * Col4))) -
450)/100

Obviously this sometimes causes a precision issues because the float
and real datatypes don't hold precise values... My problem is that
converting to the numeric or decimal datatype easily produces an error
if the precision is not big enough to hold the resulting output... So
here's my questions:

Does anyone see a problem with me maxing out the precision of the
decimal datatype (ie. use DECIMAL(38,8) across a couple dozen
inter-related formulas?

So the above code would now exec like this:

495/(CONVERT(DECIMA L(38,8),1.112) -
(CONVERT(DECIMA L(38,8),.000434 99)*((Col1 + Col2 + Col3))) +
(CONVERT(DECIMA L(38,8),.000000 55) * ((Col1 + Col2 + Col3)) * ((Col1 +
Col2 + Col3))) - (CONVERT(DECIMA L(38,8),.000288 26) * Col4))) - 450)/100

If anyone does see a problem with this approach, could you suggest
another alternative?

[Erland Sommarskog]

Tariq (ta***@inwebcom .com) writes:

I've a SQL view performing a number of very precise calculations ie.

495/(1.112 - (.00043499*((Co l1 + Col2 + Col3))) + ( .00000055 * ((Col1
+ Col2 + Col3)) * ((Col1 + Col2 + Col3))) - (.00028826 * Col4))) -
450)/100

Obviously this sometimes causes a precision issues because the float
and real datatypes don't hold precise values... My problem is that
converting to the numeric or decimal datatype easily produces an error
if the precision is not big enough to hold the resulting output... So
here's my questions:

Does anyone see a problem with me maxing out the precision of the
decimal datatype (ie. use DECIMAL(38,8) across a couple dozen
inter-related formulas?

I may be missing something, but 495 / <some basically arbitrary number>
will not be a decimal number, only a rational number. Unless you store
nominator and denominator separately, you will lose precision. With
float you will 14-16 digits of precision, which is good for many cases.
I don't really see what decimal(38,8) buys, although there is probably
not any major harm.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp