I ran into a known issue with MS SQL Server 2005 that threw me for a loop. Essentially, casting a rounded float value as a string led to the number losing significant digits when the number of significant digits was greater than three.
Ok, digest that for a minute: Values stored in the float datatype in SQL Server aren’t accurate past ten-thousands! Uh, isn’t that a big deal?
Don’t believe me? Check this out yourself:
declare @res as float
select @res = 123.123456789
select @res
select CAST(@res as float(8))
select CONVERT(FLOAT, @res)
select ROUND(@res,8)
select CONVERT(varchar, ROUND(@res, 8))
I think the real reason its broken is because the underlying datatype stores values in scientific notation then breaks the integer and decimal parts into base numeric datatypes. n x 10^x. So, 123.9994 is actually stored as 1.239994 x 10^-2 then placed into variables holding the constituent parts: 1, 239994, and -2. The rounding problem is when a numeric component (say the 239994 part) is larger than the base datatype holds. A way to hold that number is to also store it as a float, also held in scientific notation (e.g., 2.39994 X 10-5). Doing this recursively until every component numeric is stored in a short int (or any convenient datatype), works fine for storage, until you try to do math on the mess.
See the thing is, you could do the inverse and it would work fine to regenerate the original number; however, math operations on floating point numbers are much simpler when dealing with numbers already stored in scientific notation. I bet they’re making the same mistake Intel did with the original Pentium chip. Recall that the original Pentiums incorrectly stored numbers in scientific notation incorrectly past the 14 digit past the decimal. My guess? Because the number was being stored in a word (2 bytes, 16 digits) instead of a long (4 bytes, 32 digits). The 16 digits were being stored as x.yyyyyyyyyyyyyy. If there were more significant digits that 14, the Pentium chip programmers incorrectly handled these, I allege.
The problem with changing a bug like this in SQL Server is that it is probably in the original code delivered by SyBase when MS acquired the SQL Server technology from them in the early ’90s. Back then, we had very few 32-bit chips, so who cares about storing a number with more significant digits than could be stored? So the bug is in the bowels and I wonder if anyone at Microsoft understands that original code very well. The problem with fixing it is probably due to people’s memory and not the desire to get it right. The bug is just too fundamental for me to believe that no one caught it since it’s pretty obvious. Clearly they are rationalizing it away and hoping no one has to have this fixed. How else could such a fundamental bug be swept under the rug for so long?
This is just speculation, of course. Let’s hope it is fixed in MS SQL Server 2008!
