float or numeric?

[katbear]

Hi,

I have to load some data from a flat file into a table.

Several of the columns are numbers with decimal points. Here are some sample values:

1234.01
1234.0001
1234.000001

So you see what's happening here. I have some numbers with 2, 4, 6 numbers after the decimal point.

My question is this. Should I set up the table columns as FLOAT or NUMERIC(18,6)?

With Numeric, I have to specify the size of my number, such as NUMERIC(18,6). I guess I could set all of the columns to NUMERIC(18,6) even if there's less than 6 digits after the decimal point?

Confused.

Thanks

 

[gmmastros]

Float is an approximate data type. I never use them. If you set your data type to Numeric(18,6), you can still store all the numbers in the table.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[FlaBusInt]

Remember - float and real are approximate data types. This means that they don't always store the exact number. For example, 12.0099 might be stored as 12.01. If you need the numbers for precise calculations, you should use numeric. Otherwise, I think float requires slightly less storage space (something like 1-2 bytes) to store the number.

In my group, we have a best practice of always using numeric. That way, we will never be inadvertently burned by a rounding error.


- Steve

 

[ptheriault]

Kat,
To add to what George said, Float and real are subject to rounding errors.
It is best not to use them.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[SQLSister]

I'm with everyone else, I would never under any circumstances create a field using float or real.

Questions about posting. See faq183-874

 

[katbear]

Thanks for the clarification!

As it were, I set up all my number columns as numeric(18,6) so that way I don't have to bother with how many digits after the decimal point. They're all covered.

 

[SQLSister]

Give it a couple of extra places after the decimal more than you think you need.

Questions about posting. See faq183-874