Float or Decimal?

[Guillermo Casta?o A]

Hi, i want to know which is the preferred datatype for numeric fields.
Decimal or float? and why?

Are there any performance, acurate reasons to pick one of them?

thanks

[Foo Man Chew]

Float does some weird rounding things... try these out:

DECLARE @a FLOAT, @b FLOAT
SELECT @a = 3.5, @b = 4.2
SELECT @a + @b

Compared to these results:

DECLARE @a DECIMAL, @b DECIMAL
SELECT @a = 3.5, @b = 4.2
SELECT @a + @b

"Guillermo Casta?o A" <guille...@hotmail.com> wrote in message
news:9350d78d.03112...@posting.google.com...

[Ashish]

Foo Man Chew wrote:

howcome

DECLARE @a DECIMAL(18,5), @b DECIMAL(18,5)

SELECT @a = 3.5, @b = 4.2
SELECT @a + @b

= 7.7000

but

DECLARE @a DECIMAL, @b DECIMAL
SELECT @a = 3.5, @b = 4.2
SELECT @a + @b

= 8 ?

decimal + decimal = int ?

is decimal declaration having no decimal assigned to it by default ?

-ashish

[Fabian Castro]

>-----Original Message-----
>Hi, i want to know which is the preferred datatype for
numeric fields.
>Decimal or float? and why?

You should avoid float. At least working on visual basic I
have gotten decimals "alive" after the fith decimal
position. Even when I have set the field with an entire
number.

like update some column = 7
and when I read the equialent txtfield in a VB form it is
7.0000056.... etc.

bye fabianc.

>
>Are there any performance, acurate reasons to pick one of
them?
>
>thanks

>.
>

[Louis Davidson]

No, decimal + decimal = decimal. Look at the output from SELECT @a. It
doesn't have a decimal point either. The default scale is 0, which looks
like an integer.

--
----------------------------------------------------------------------------
-----------
Louis Davidson (dr...@hotmail.com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

"Ashish" <ash...@mailmenot.com> wrote in message
news:OL5eS9ts...@TK2MSFTNGP09.phx.gbl...

[Louis Davidson]

What are you doing with them? Floating point numbers are generally used for
calculations, measurements, etc, where a small error is allowable. They are
faster than numerics, because floating points are dealt with by the FPU
directly, while numerics are database specific (integers are manipulated
with registers, so they are even faster) and use CPU to do math. However,
you wouldn't want to store monetary values in them (use money datatype if
possible, as it is based on integer datatypes, and does not lose any
precision.

--
----------------------------------------------------------------------------
-----------
Louis Davidson (dr...@hotmail.com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

"Guillermo Casta?o A" <guille...@hotmail.com> wrote in message
news:9350d78d.03112...@posting.google.com...

[Steve Kass]

Louis,

Money doesn't lose precision ... *if* it is only used for addition,
subtraction, and
multiplication by integers.

Unfortunately, the careful rules by which decimal calculations are
designed
to do their best to hold both good precision and scale are out the
window when
it comes to money.

Suppose you pay $2.57 per 10,000 widgets. How much are you paying for
each?

select $2.57/10000

Oh-oh. Money has it's place, but it can also lead to very bad results.
You might
argue that 0.000257 isn't supposed to be well-represented as money,
which only
has 4 decimal places of precision, but there are plenty of other money-type
problems - for reasons I can't understand, money/money is money, not pure
number, and the precision can't be handled correctly. You might want to
divide monetary values to figure out, say, what fraction $1.23 is of a
$2137 bill:

select $1.23/$2137

It's off by a lot - in my opinion, because the answer is wrongly typed
as money.
So beware.

-- Steve Kass
-- Drew University
-- Ref: D3F842EE-4910-40CA-A345-CAF0A63A9CE8