June 4, 2009 at 7:36 pm
[font="Verdana"]We are importing a source system's data into our data warehouse, and the source system uses FLOAT values to store time (duration) as hours.
My past objection to FLOAT has always been that I understood it couldn't represent certain numbers due to the physical layout of how it is defined. I'm going from memory here, understand! So I thought it couldn't represent 0.3.
I've gotten into the habit of challenging my own assumptions, and I wrote up some little tests to see whether that is, in fact, the case. It's not. Or at least, it seems not to be.
declare @x float, @y decimal(18,10);
set @x = 0.0009;
set @y = 0.0009;
select
@x / (cast(0.3 as float) * cast(0.3 as float)),
@y / (cast(0.3 as decimal(18,10)) * cast(0.3 as decimal(18,10)));
In both cases, I get back 0.01 (the correct answer).
So I'm starting to wonder: is FLOAT really as bad as I thought? Or is it okay to use? Does anyone have any recommendations (for or against)? If you have issues with it as a data type, can you share some example code that demonstrates that issue?
One issue I can demonstrate is the addition of very large and very small numbers. For example:
declare
@x decimal(16,6), @y decimal(16,6);
set @a = 1000000000.0;
set @x = 1000000000.0;
set @b-2 = 0.000001;
set @y = 0.000001;
select
@x + @y;
This gives 1000000000 and 1000000000.000001. However, that's such a rare occurence that is it really an issue?
If anyone has some good links or articles on the issues, pleas feel free to post them here.
Thanks!
[/font]
June 5, 2009 at 2:13 am
A quick look at BOL explains the at-first weird looking addition result in your second example. FLOAT datatype defaults to 53 bits of mantissa data (the maximum for the type), stored in eight bytes (gotta leave some bits for exponent and sign....), with a precision of 15 digits. Adding the 10 digit integer value "1000000000" to the six digit fractional value ".000001" would require 16 digits of precision for the sum, so it doesn't quite fit in a FLOAT result.
June 5, 2009 at 11:13 pm
Depending on what you're doing, FLOAT can be great. For example, if you add 1/3 three times, you should end up with a nice solid "1", right? Well, the only way you can get there is to ensure you have enough precision and scale and then do the correct rounding. For example...
[font="Courier New"]DECLARE @F FLOAT
SET @F = .3333
DECLARE @D DECIMAL(4,2)
SET @D = .33 --Simulates precision/scale lost during a calculation
SELECT Number,
[Number*@F] = Number*@F,
[Number*@D] = Number*@D,
[STR(Number*@F,4,2)] = STR(Number*@F,4,2),
[ROUND(Number*@F,2)] = ROUND(Number*@F,2),
[CONVERT(DECIMAL(4,2),Number*@F)] = CONVERT(DECIMAL(4,2),Number*@F)
FROM Master.dbo.spt_Values
WHERE Number <= 10
AND Type = 'P'
[/font]
Although FLOAT usually provides enough precision for whatever you want, it can do some pretty nasty things to the accuracy of your answers if you don't expect a scale change kind of like the resolution you can loose on a slide rule as the numbers get larger. Just something to keep in mind.
I hope Sergiy shows up on this question... he's much better equipped to explain why FLOAT is a really good thing for most scientific calculations other than it handle crazy large or small numbers very easily.
FLOAT isn't absolutely necessary get precision. You can always use some DECIMAL declaration with at least 15 decimal places (just about required on mortgage and other interest calculations). The key is to do all the calculations with the largest precision and scale possible and the format the answer.
The reason why FLOAT displays data so oddly to the human eye is because although it displays as decimal information, it's really a binary number behind the scenes.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 11, 2009 at 3:45 pm
[font="Verdana"]Thanks for your input guys. I'll be less hard on float going forward, I think.[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply