MS SQL, Shades of Vista

I've bitched about MS SQL Server before. This shows no sign of lessening...

Consider this simple little code example.


create table #FloatTest (seq [int] IDENTITY(1,1), a float(24), b float(24) )

insert into #FloatTest(a) values(0.1)
insert into #FloatTest(a) values(0.2)
insert into #FloatTest(a) values(0.7)
insert into #FloatTest(a) values(0.1)

update #FloatTest
set b = ( select sum(a) from #FloatTest b where b.Seq<=a.Seq )
from #FloatTest a

select a,b from #FloatTest order by seq

a b
------------- -----
0.1 0.1
0.2 0.3
0.7 1
0.1 1.1


These are expected results. However, float is hiding something. It's hiding the sad fact that Microsoft's flagship Enterprise product is confused by simple addition.

Change one line like so:
create table #FloatTest (seq [int] IDENTITY(1,1), a float(24), b float(53) )

This is isn't a change to stored value a, but rather just the bucket size. The results should be identical. Of course, they're not.

a b
------------- -----------------
0.1 0.100000001490116
0.2 0.300000004470348
0.7 0.999999992549419
0.1 1.09999999403954


I was, of course, made aware of this when attempting do something like "select * from #FloatTest where floor(b)=b" and getting no values. One fix, the one I'll probably have to go with, is a casting hack like "select * from #FloatTest where floor(cast(b as float(24)))=cast(b as float(24))".

Yeah, b is still 0.999999992549419, but that's what I have.

MS SQL Server sure is easy to maintain. That GUI is cool. Backup and restore is a no brainer. Too bad it's buggier than picnic potato salad at high noon.

Anyway, that's my Microsoft journey for today. For an easy, play at home version, try this: select cast(cast(0.1 as float(12)) as float(53))

Yep, still buggy.