SQLServer float の怪

SQLServer 2005, 2008 で確認。なんということでしょう!!  🙁

select cast(cast(55 as float) / cast(100 as float) * cast(100 as float) as int)
--> 55
select cast(cast(56 as float) / cast(100 as float) * cast(100 as float) as int)
--> 56
select cast(cast(57 as float) / cast(100 as float) * cast(100 as float) as int)
--> 56 なぬっ!?
select cast(cast(58 as float) / cast(100 as float) * cast(100 as float) as int)
--> 57 なぬっ!? 
select cast(cast(59 as float) / cast(100 as float) * cast(100 as float) as int)
--> 59
select cast(cast(60 as float) / cast(100 as float) * cast(100 as float) as int)
--> 60

最後にcast as int で丸めなければ期待通りの結果です。

結論!丸める可能性がある場合はfloatを使わない。