After looking for quite a while (it is unbelievable, no?) I have to accept that Microsoft’s SQL Server 2000, 2005 and 2008 do not fully support IEEE-754 floating point numbers. Specifically NaN (Not-a-Number) and +/- Infinity are not allowed. While the 2000 Server seemingly allows such values to be inserted, but breaks badly afterwards, the newer versions disallow inserting such values. Here is the MS Connect article requesting the feature. There is also the documentation about float data and a read-between-the-lines hint in the documentation for XPath numbers. The article describing floats is very circumspect about this:
The behavior of float and real follows the IEEE 754 specification on approximate numeric data types.
[emphasis mine]
The XPath article states it clearly:
However, float(53) is not exactly IEEE 754. For example, neither NaN (Not-a-Number) nor infinity is used.
.
For completeness, here’s a little review about other DMBS’ support for NaNs and Infinity:
- MySQL does what it always does if it encounters “strange” values: according to this comment by Carl Fischer (bottom of the page), MySQL transforms the special values quietly to zero when writing them into the table.
- Oracle (starting at version 10g) supports all IEEE754 single and double precision values and provides constants for NaN and Infinity in its SQL implementation. This is implemented using the BINARY_FLOAT and BINARY_DOUBLE data types.
- PostgreSQL uses the underlying system’s float representation and allows input of NaN and infinite values.