Re: float storage and usage

On Thu, 7 Jun 2007 21:54:25 +0000 (UTC), Erland Sommarskog
<esquel@xxxxxxxxxxxxx> wrote:

Jeff Kish (jeff.kish@xxxxxxx) writes:
I need to convert some columns of type numeric(12, 0) to hold floating
point information scale and precision I can't determine in advance
(customer data can vary wildly) so I wanted to use the datatype that
offers the maximum scale and precision..
I'm targeting sql server 2005 systems (not 2000).

It seems my choices are real and float, and the docs seem to indicate that
float offers with widest ranges.

I'm trying out using the 'float' for the new data type as the default
precision is said to be 53.. Does this mean the total number of digits
is up to 53?

The total number of binary digits in the mantissa. Which in decimal
terms means something like 14-16 digits in precision. The scale can
range from 1E308 to 1E-308.

I don't know if there is anything else I need to take into account since
these two columns are part of a primary key, and I supposed, therefore,
are indexed.

Putting a float into a primary key is definitely not recommendable. Float
is an approxamite data type, meaning the same decimal value can be
represented in more than one way, depending on how you arrived to the
result. It would be a (correct) knee-jerk reaction from anyone who
reviewed your schema to flag float values in a PK constraint as dubious.

Since I don't know your customer's data, it's difficult to say what
would be the best. But if all values are integer, that is there is no
decimal portion, decimal(38,0) is probably the best. On SQL 2005 SP2,
there is a new table option, "vardecimal storage format". When this is
in force, decimal values do not take more space than necessary. This
option is only available in Enterprise Edition.
thanks.
the data represents coordinates on images that can vary vastly in
scale and precision.
The primary key well... each row in the table represents a text
display on an image.. the row has columns for:
book
page
label
xcoord
ycoord
because the same label can appear several times on one page in a book,
the coords are included.

one page might vary from -1.234565 to 1.3234343 in extents, placing
all coordinates in that range, and another might be a different type
of image and range from -10245 to 10245.

it seems to work ok, though i understand it is not optimal.
it is of course, a legacy ...
thanks again
JEff
.

Relevant Pages

  • Re: float storage and usage
    ... offers the maximum scale and precision.. ... I'm targeting sql server 2005 systems. ... float offers with widest ranges. ...
    (comp.databases.ms-sqlserver)
  • Re: float storage
    ... column to be modified must be empty to decrease precision or scale ... which I guess implies that number has more capacity than float. ... To increase the scale one must decrease the precision. ... or any declaration where Y was greater than X. ...
    (comp.databases.oracle.misc)
  • Re: IBM-MAIN Digest - 26 Apr 2008 to 27 Apr 2008 (#2008-118)
    ... 123.0000000E0 would be FLOAT DECIMAL. ... Binary used the suffix B, and the value was given in binary digits ... base, scale, precision, or mode. ...
    (bit.listserv.ibm-main)
  • Re: new here, my lang project... PT2
    ... The scale is limited by the resolution of ... I see this as a precision vs. accuracy issue. ... > physics core ... > just I am not sure how necessary the partitioning is at present. ...
    (comp.object)
  • Re: Losing Precision from FLOAT in DBD::Informix
    ... The versions of CSDK and IDS are also not ... STSM, Informix Database Engineering, IBM Data Management ... >> are declared FLOAT. ... >> - where and why is the precision lost? ...
    (perl.dbi.users)