Posted by Erland Sommarskog on 06/07/07 21:54

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

[Back to original message]