Click here to Skip to main content
15,860,859 members
Articles / Programming Languages / SQL
Tip/Trick

MSSQL doesn’t (completely) support IEEE754 floating points (NaNs and infinity)

Rate me:
Please Sign up or sign in to vote.
4.33/5 (2 votes)
4 Jan 2010CPOL1 min read 21.7K   3   1
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...
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.


Originally posted on my blog[^].

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Founder http://dasz.at/ -- Software for Humans
Austria Austria
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Christian Leutloff26-Jul-12 23:44
Christian Leutloff26-Jul-12 23:44 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.