float storage and usage

[Jeff Kish]

Greetings.

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?

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.

thanks
Jeff Kish

[Erland Sommarskog]

Re: float storage and usage

Jeff Kish (jeff.kish@mro. com) 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.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Jeff Kish]

Re: float storage and usage

On Thu, 7 Jun 2007 21:54:25 +0000 (UTC), Erland Sommarskog
<esquel@sommars kog.sewrote:

>Jeff Kish (jeff.kish@mro. com) 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

[Erland Sommarskog]

Re: float storage and usage

Jeff Kish (kishjjrjj@char ter.net) writes:

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.

Could you have coordinates that are 1E12 or 1E-12 as well? I would
expect that is after all some practical limit. In this case you could use
something like decimal(20,10). Or may be varchar is an alternative? I
would definitely avoid float.

Yet an alternative is some roll-your-own decimal. That is, you would
save the coordinates as integer, and you would store the scale separately,
possibly in a table with the page as key.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Jeff Kish]

Re: float storage and usage

On Fri, 8 Jun 2007 21:07:04 +0000 (UTC), Erland Sommarskog
<esquel@sommars kog.sewrote:

>Jeff Kish (kishjjrjj@char ter.net) writes:

>the data represents coordinates on images that can vary vastly in
>scale and precision.

<snip>

>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.

>
>Could you have coordinates that are 1E12 or 1E-12 as well? I would
>expect that is after all some practical limit. In this case you could use
>something like decimal(20,10). Or may be varchar is an alternative? I
>would definitely avoid float.
>

thanks.
so avoid float because it is a non exact storage and that makes it dubious for
part of a key, or is it just because floats are inherently bad in a pk for
performance reasons?

>Yet an alternative is some roll-your-own decimal. That is, you would
>save the coordinates as integer, and you would store the scale separately,
>possibly in a table with the page as key.

I really would like to/need to stay with some standarad / built in type.
I'll take a peed at the decimal... maybe I can make due with them.
why are they better than floats for the pk cols?
regards
Jeff
Jeff Kish

[Erland Sommarskog]

Re: float storage and usage

Jeff Kish (jeff.kish@mro. com) writes:

so avoid float because it is a non exact storage and that makes it
dubious for part of a key, or is it just because floats are inherently
bad in a pk for performance reasons?

The former. For performance it is as good as any other bit pattern. But that
is of little interest when you may fail read rows because your input bits
does not match the table bits.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Gert-Jan Strik]

Re: float storage and usage

Jeff Kish wrote:

>
On Fri, 8 Jun 2007 21:07:04 +0000 (UTC), Erland Sommarskog
<esquel@sommars kog.sewrote:
>

Jeff Kish (kishjjrjj@char ter.net) writes:

the data represents coordinates on images that can vary vastly in
scale and precision.

<snip>

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.

Could you have coordinates that are 1E12 or 1E-12 as well? I would
expect that is after all some practical limit. In this case you could use
something like decimal(20,10). Or may be varchar is an alternative? I
would definitely avoid float.

thanks.
so avoid float because it is a non exact storage and that makes it dubious for
part of a key, or is it just because floats are inherently bad in a pk for
performance reasons?

Dubious for (part of) a key. See below

Yet an alternative is some roll-your-own decimal. That is, you would
save the coordinates as integer, and you would store the scale separately,
possibly in a table with the page as key.

I really would like to/need to stay with some standarad / built in type.
I'll take a peed at the decimal... maybe I can make due with them.
why are they better than floats for the pk cols?

The potential problem with floats is their inexact nature. Depending on
your hardware the value may differ (slightly). This can cause problems
when moving to a different server (which you might have to do if there
is a hardware problem).

Because of that you basically should not use them for keys.

HTH,
Gert-Jan

regards
Jeff
Jeff Kish