We all know that float is type just approximate value. There is how can we use that to set up a trap for ourselves.
Some times ago, I've wrote some script aggregating measures, that was - at one moment - grouping after values.
Just imagine my surprise, when I found that in resultset were two, different, values 457.84!
As I worked it out, in my temp table, created by
SELECT INTO
that problematic VALUE column was assigned FLOAT type.
That both values 457.84 was, indeed, numbers with different digit at 15th place after decimal!
Generally, for strict comparison, FLOAT (REAL too) is poorly suited. To see it clearly, try:
DECLARE @f FLOAT
SET @f = 1.223
SELECT @f, CAST(@f AS DECIMAL(38, 30)), CAST(@f+1 AS DECIMAL(38, 30))
Thank you for your attention.
-- Adam Tokarski --