The ceiling() may be lower than I thought...
Suppose you have a column in table that's type float and another that's an actual primary key. You want to find the next whole number for the float column. One way to do this is to find the maximum value for the float column, add some extremely small number to it and then use the ceiling function on that result to get desired value.
Of course “extremely” small might be much larger than you expected. As I read Books-On-Line, I see that the range for float(53) is 2.23E -308 through 1.79E + 308. So, I'm writing a procedure that's something like this:
declare @epsilon float(53)
declare @next float(53)
declare @projidnbr int
set @epsilon = 2.23E-308
set @projidnbr = 45
set @next = (select max(RFInbr) from
dbo.ReqstForInfo where projidnbr=@projidnbr) + @epsilon
select @epsilon,@next,ceiling(@next)
Lo and behold, it doesn't work. The max(RFInbr) = 3.0, and this function returns 3.0 as the next value. Of course, if I change my value for @epsilon to 2.23-16, it does work. Why? Yet again, I didn't read the whole entry. The range for float(53) is 2.23E -308 through 1.79E + 308 with 15 digits of precision.
I so wanted to rant about this being a bug. It's not. In my heart of hearts, I knew that this correct. Remember that generally, the number of digits of precision of an IEEE 754 is equal to one less than the number of 8-bit bytes used for storage doubled (although implementations might differ) For the application I'm working on, it is very unlikely that the maximum RFINbr would ever have more than three decimals in length, so 2.23E-16 is for all practical purposes just as good as 2.23E-308. This started me thinking, hey, this would be a great excuse reason to use the CLR features of Yukon. Turns out that it doesn't help -- same size and same precision for doubles there.
Anybody else out there doing higher-precision stuff with SQLServer, and how?