SQL Server Floating Point – Why Allow float(53) Outside Specified Range?

floating pointsql serversql-server-2005sql-server-2008

I have a particular table with a column data type of float(53), non null. According to the Microsoft Documentation, the smallest value above 0 that can be stored in this data type is is 2.23E-308. However, I am able to store a value much smaller, as small as 4.94065645841247E-324. See the following image of proof that SQL server can store and retrieve a value outside the range specified in the documentation:

Query showing very small floating point number

When I run a query such as the following:

select 4.7E-309

SQL Server says: Warning: the floating point value '4.7E-309' is too small. It will be interpreted as 0.

The value inserted in the screenshot was inserted via the C++ ADO interface using record binding.

I have a few questions:

Why does SQL server handle values outside the range specified in the documentation?
Similarly, why does SQL Server issue a warning when an "out of range" value is in the query, when clearly the database can store values out of that range?

How are floating points stored on SQL database tables, and are there any SQL server settings that would strictly enforce the range on one SQL server as opposed to another?

I'm trying to troubleshoot an issue where an ADO record binding was (possibly) rejected due to the value being out of range, however all my test to reproduce the issue (on a different SQL server instance) show that SQL accepts values much beyond the limits specified in the documentation. Any information on how floating points are stored and handled in SQL (especially with relation to something like .NET or C++) would be much appreciated.

I have tested this with both SQL server 2005 and 2008 with the same results.

Best Answer

I suspect this is a validation error (ie. a bug). Wouldn't be the first, specially in 2005-2008 (there was a big data purity fixes push in 2012 I think). I suspect that a DBCC CHECKDB WITH DATA_PURITY will complain about the value.

SQL Server stores floats as IEEE 754, same way as .Net and C++ represents them. 64 bit can represent values way above and bellow the 2.23E-308, as you already found out about 4.94065645841247E-324 (ie. 0x0000000000000001 interpreted as a float). But such values fail another IEE754 requirement, namely reproducibility:

The standard requires operations to convert between basic formats and external character sequence formats. Conversions to and from a decimal character format are required for all formats. Conversion to an external character sequence must be such that conversion back using round to even will recover the original number. There is no requirement to preserve the payload of a NaN or signaling NaN, and conversion from the external character sequence may turn a signaling NaN into a quiet NaN.

The original binary value will be preserved by converting to decimal and back again using:

  • 5 decimal digits for binary16
  • 9 decimal digits for binary32
  • 17 decimal digits for binary64
  • 36 decimal digits for binary128

This requirement places the limit at documented numbers:

  • 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308

The fact that ADO binding allows basically any combination of bits to be passed as the value and the SQL Server accepts the binding buffer w/o validation results in the behavior you noticed. Technically, the data you inserted this way is a corruption. Again, I suspect that a) newer versions of the engine validate the buffer before write (in other words they catch the invalid value) and b) WITH DATA_PURITY check will report the problem (even on 2005/2008/R2 versions).

Related Question