sql server programming: We've come across a serious issue with using these approximate data
types. Basically whoever used them to store our monetary values should
be shot, but moving on .....

Has anyone actually chosen them (apart from in error) for any
application or come across a good example to use them on, and why ?

I think he realizes the problems with real and float, and is wondering if
any of us actually use it on purpose and/or for any good reason. I think
mathematicians (e.g. Steve K.) might have pratcical answers for him, but I
don't.

--
http://www.aspfaq.com/
(Reverse address to reply.)




[quoted text, click to view]

David
If you give us an example we would solve the problem

What kind of data you are going to deal with?

Did you consider to use DECIMAL(18,2) datatype?



[quoted text, click to view]

David,

No data type can represent every numerical value exactly and perform
every numerical calculation exactly. Float and decimal types have their
own separate quirks and rules, and there isn't one best type, even for
monetary values ([money] has its own serious problems, if multplication
or division are involved).

The rules for typing expressions involving decimal values are
complicated and will lead to some suprises, because there are over 700
different decimal types in T-SQL. Here's one example of where float is
a better choice:

declare @a decimal(18,4)
set @a = 0.0003
select
sum(a)*sum(a)*100,
100*sum(a)*sum(a)
from (
select @a a
union all
select @a
) x
go

declare @a float
set @a = 0.0003
select
sum(a)*sum(a)*100,
100*sum(a)*sum(a)
from (
select @a a
union all
select @a
) x
go

If you have a particular situation where float is causing problems, the
solution may be to use a decimal type instead, but it may also be better
to reframe the calculation and stick with float.

Steve Kass
Drew University

[quoted text, click to view]