Click here to Skip to main content
Click here to Skip to main content

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

By , 4 Jan 2010
Rate this:
Please Sign up or sign in to vote.
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)

About the Author

DavidSchmitt
Founder http://dasz.at/ -- Software for Humans
Austria Austria
No Biography provided

Comments and Discussions

 
GeneralMy vote of 5 PinmemberChristian Leutloff26-Jul-12 23:44 

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

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

| Advertise | Privacy | Mobile
Web01 | 2.8.140415.2 | Last Updated 4 Jan 2010
Article Copyright 2010 by DavidSchmitt
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid