6

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.

1 Answer 1

7

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).

3
  • For an in dept discussion about reproducibility, read Floating-Point Determinism. Basically, you can represent extreme values, but if you go outside the specified range you end up with different results for arithmetic operations involving such values on different systems (ie. different CPU). You run the report on machine A and you get one value, same report on same DB on machine B you get a different result. Sep 1, 2016 at 20:04
  • Thanks for the response. I found something interesting after further investigating. When I tried importing the record into a database running on a different server, I get an error. The server that rejects the record is running 9.00.3042.00. The original server I was working with is running 9.00.3310.00. I tried transferring a the record to another server running 9.00.3310.00 and it accepted the record. It seems somewhere between version 9.00.3042 and 9.00.3310 the validation check was removed.
    – Brad
    Sep 2, 2016 at 17:29
  • 2
    Some additional information for anyone interested in the future. It looks like this was a result by the following bug fix: support.microsoft.com/en-ca/kb/942904
    – Brad
    Sep 2, 2016 at 19:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.