FLOAT definitely has its useful place in the world but shouldn't be used generically nor even for most things that require decimal math because of the very definition of the FLOAT datatype in SQL Server. From the following link...

https://msdn.microsoft.com/en-us/library/ms173773.aspx

... we get the following definition of what the FLOAT datatype is. The emphasis is mine...

A great example came up in a post just two days ago. It does the simple calculation of...

A B

--- - ---

C C

Assigning some simple values, as below, figure out the answer in your head or using your favorite 4 function calculator and you find that it works out to be ...

A B 7 2

--- - --- = --- - --- = .7 - .2 = .5

C C 10 10

Now, let's put that to code using the FLOAT datatype. Looking at the code below and knowing that the correct answer to the problem is .5 and so the result of the code should be "GE.5", explain why it isn't.

--===== Create variables and assign values

DECLARE @a FLOAT = 7

,@B FLOAT = 2

,@C FLOAT = 10

;

--===== Solve the problem as stated in the previously mentioned formula

-- and use CASE to determine if the answer is >= .5 or less than .5.

SELECT CASE

WHEN @a/@C - @b-2/@C >= 0.5 THEN 'GE.5'

ELSE 'LT.5'

END

;

What's really frustrating is that the following code "proves" that it should all work out just fine.

--===== Create variables and assign values

DECLARE @a FLOAT = 7

,@B FLOAT = 2

,@C FLOAT = 10

;

SELECT @a/@C, @b-2/@C, @a/@C - @b-2/@C, CAST(.5 AS FLOAT)

;

Results:

---------------------- ---------------------- ---------------------- ----------------------

0.7 0.2 0.5 0.5

(1 row(s) affected)

Microsoft did a pretty good job of masking the "problem" by "correcting" the display but that doesn't correct the actual results. You can see the "problem" that occurs during the subtraction using the following code, which does the FLOAT math and then displays the results as DECIMAL so that you can actually see why the CASE example failed to provide the correct answer. (I have "problem" in quotes because its not a problem if you understand BINARY floating point math and, ironically, how to round to get the actual correct answer as a true value instead of just a displayed value).

--===== Create variables and assign values

DECLARE @a FLOAT = 7

,@B FLOAT = 2

,@C FLOAT = 10

;

SELECT CAST(@A/@C AS DECIMAL(38,28))

,CAST(@B/@C AS DECIMAL(38,28))

,CAST(@A/@C - @b-2/@C AS DECIMAL(38,28))

;

Results:

--------------------------------------- --------------------------------------- ---------------------------------------

0.7000000000000000000000000000 0.2000000000000000000000000000 0.4999999999999999400000000000

(1 row(s) affected)

You see, FLOAT in SQL Server is NOT actually DECIMAL math behind the scenes. It's BINARY math and, with the limited precision of "only" 15 digits, not all DECIMAL numbers can successful be converted to BINARY with 100% accuracy. Hence the term "imprecise" in most peoples definition of FLOAT and "approximate" in the MS definition. Here's some more "fun". WYSI[font="Arial Black"]N[/font]WYG 😉 The column on the left is what is displayed and the column on the right is the number actually used.

--===== Create variables and assign values

DECLARE @a FLOAT = 7

,@B FLOAT = 2

,@C FLOAT = 10

;

SELECT LooksLike = CAST(number AS FLOAT)/10

,ActuallyIs = CAST(CAST(number AS FLOAT)/10 AS DECIMAL(38,28))

FROM master.dbo.spt_values

WHERE type = 'P'

AND number BETWEEN 0 AND 20

;

Results:

LooksLike ActuallyIs

---------------------- ---------------------------------------

0 0.0000000000000000000000000000

0.1 0.1000000000000000000000000000

0.2 0.2000000000000000000000000000

0.3 0.3000000000000000000000000000

0.4 0.4000000000000000000000000000

0.5 0.5000000000000000000000000000

0.6 0.6000000000000000000000000000

0.7 0.7000000000000000000000000000

0.8 0.8000000000000000000000000000

0.9 0.9000000000000000000000000000

1 1.0000000000000000000000000000

1.1 1.1000000000000001000000000000

1.2 1.2000000000000000000000000000

1.3 1.3000000000000001000000000000

1.4 1.3999999999999999000000000000

1.5 1.5000000000000000000000000000

1.6 1.6000000000000001000000000000

1.7 1.7000000000000000000000000000

1.8 1.8000000000000000000000000000

1.9 1.8999999999999999000000000000

2 2.0000000000000000000000000000

(21 row(s) affected)

And you say FLOAT avoids the "tricky" problem of rounding? 😀 Check out what MS says about FLOAT and rounding at the following URL...

https://msdn.microsoft.com/en-us/library/ms190476.aspx

... where it clearly states that FLOAT has a rounding "problem" (emphasis is mine).

Sounds like a "Best Practice" to me.

Also remember that FLOAT has a fixed precision of 15 digits with a floating decimal point. As the number of digits on the left side of the decimal points goes up, you lose scale... which is another kind of (sort of) "rounding" problem... the very problem that you're trying to avoid.

This is also why Luis said "it's lazy" of developers to use FLOAT for everything (or most anything) and I agreed. If they had taken the time to look all that stuff up, they wouldn't be so adamant about using FLOAT to make things "easy". Using the correct datatypes for the precision and scale expected is not a form of pre-optimization... and it's not just a "Best Practice". It's absolutely necessary for coming up with the right bloody answer. 😉

P.S. You should also avoid the use of any of the "MONEY" datatypes and small scale DECIMAL types for any work with any operators other than simple addition and subtraction.